How to Delete or Target All Parameters Created in the SQL Editor

If you set parameters using the Query Builder editor in VisualStudio,
Is there a way to exclude the @code from the WHERE clause or cover everything?

SELECT code, unit price
FROM COMMODITY MASTER
WHERE code=@ code

to SelectQuery in SqlDataSource I wish I could use the same function as SqlCommand's command.Parameters.Add.
Currently, we use the following SQL statements.

SELECT code, unit price
FROM COMMODITY MASTER
WHERE (@code IS NULLOR [code] = @code)

.net sql

2022-09-29 22:08

2 Answers

SQL statements can be changed dynamically because they are strings, but using some framework is one way.
For example, the .NET Framework provides Entity Framework.This allows you to dynamically build SQL statements with .NET code.EntityDataSource equivalent to SqlDataSource is also available. (I've never used it before, so I don't know if it's appropriate...)

Even with these, internally, SQL statements like those written by the questioner will still be generated and executed on the DB engine.

2022-09-29 22:08

I found that I could write dynamic SQL using stored procedures (T-SQL).
I think the following SQL statements are better and don't need complicated code.

 WHERE (@codeIS NULLOR[code]=@code)

For dynamic T-SQL, the following branches were possible:

CREATE PROCEDURE [ado].[item]
@ Code nvarchar(30)

IF@ code IS NULL
  BEGIN
  SELECT code, unit price
  FROM COMMODITY MASTER
  END

ELSE    
  BEGIN
  SELECT code, unit price
  FROM COMMODITY MASTER
  WHERE code=@ code
  END

2022-09-29 22:08

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656