SG SQL Server Usergroup

Is there any way by which I can create my SQL inside the stored procedure and execute it at the end of the procedure.

Scanario is : 

There are five parameters to the stored procedure and a sql has to generate with using all these parameters.

if the parameter value is provided it has to included in the and cluase, otherwise leave it as it is

just the way I do it in my DAL.... I ask user to pass the parameter, create the query based on the passed parameter and execute it on the datareader to get the datatable as a result.

Hi,

Yes, you can do this. Use the following code to create dynamic SQL in a procedure. I am writing a simple example for your reference.

------------------------------------------------------------------------

Create proc proc_Test

@param1 varchar(100),  -- some parameter value

@param2 char(1), --use this as a condition, if this value is 'Y' then use @PARAM1 in the where clause else use @PARAM3 in the where clause.

@param3 varchar(100)   ---some parameter value

As

begin

Declare @sql varchar(1000)  ----Declare a string variable

set @sql = 'Select col1, col2, col3 from Test_table where 1=1 '   ---set the basic text in the @sql variable

if @param2='Y'   ---Check the condition, if @param2 = 'Y' then

------- append the @sql variable using this format. Make sure you don't put the variable within single quotes, else it will take it as constant. To use the value of the variable, close the quotes and use + sign to append value. Use the same approach for @PARAM3 

      set @sql = @sql + ' and col1 = ' + @param1    

else

     set @sql = @sql + ' and col1 = ' + @param3

exec (@sql)    ----Use this statement to execute the value of @SQL

print (@SQL)   ----You can take a look at the contents of @SQL and ensure that your query is created dynamically and correctly with all values.

end

------------------------------------------------------------------------

Hope this will be helpful to you. If any doubt, let me know.

Thanks

Sudhir

You can use the EXEC(@mySQLString) or EXECUTE(@mySQLString). Do take note of the security (ownership) issues though.

If you trying to create a search interface for the user, allowing users to specify their search criteria, you can always use the LIKE @myParameterValue and specify the default value for @myParameterValue as "%". Take note that this does not cover null values in your tables. This method is not recommended if performance is a major factor (i.e. having more than hundreds of users at the same time and millions of recrods).