Search code examples
sqldynamic-queries

how to pass variables this in dynamic query in sql

i using the dynamic query to pass the variables

select a.TableName, COUNT(a.columnvalue) as '+'count'+' from Settings a
where a.ColumnValue in ('+ @columnvalue +') and a.Value in (' + @value +')

the @columnvalues = 'a','b','c'
@value ='comm(,)','con(:)'

how to pass this in dynamic query

any idea???

Solution

  • I would use the sp_executesql command.

    Some more documentation is here: http://msdn.microsoft.com/en-us/library/ms188001.aspx

    Basically, you define a sql query, and parameter list, and then pass those in along with your actual parameters into that method.

    So, something like this (real basic)

    CREATE PROCEDURE dbo.yourProc
  @customerId INT
AS
DECLARE @sql NVARCHAR(1000)
SET @sql = 'SELECT * FROM Customers WHERE CustomerId = @customerId'

DECLARE @params NVARCHAR(1000)
SET @params = '@customerId INT'

EXEC dbo.sp_executesql @sql, @params, @customerId