Passing parameters for dynamically created SQL queries

There are times that we need to create SQL queries dynamically and pass values to parameters. You can always assign values with a syntax similar to “… WHERE ColumnName = ‘ + @Value + ‘ and …”. But the disadvantage of using the above syntax is, that you have to provide correct formatting according the data type of the column.

This can be prevented using this type of solution. (Assume you have to get a count of records which matches a certain condition which will be provide outside the query)

 

   1: declare @Value            as nvarchar(50)

   2: declare @Sql            as nvarchar(100)

   3: declare @Parameters        as nvarchar(100)

   4: declare @Count            as int

   5:  

   6: set @Value = 'ValueX'

   7: set @Sql = 'set @Count = (select count(*) from TableName where ColValue = @Value)'

   8: set @Parameters = '@Count int output, @Value nvarchar(100)'

   9:  

  10: exec sp_executesql @Sql,@Parameters,@Count output,@Value

  11:  

  12: select @Count