how to pass variables this in dynamic query in sql

sql dynamic-queries
Innova picture Innova · Jun 11, 2010 · Viewed 18.1k times · Source

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???

Answer

Jim B picture Jim B · Jun 11, 2010

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

Related questions

My SQL Dynamic query execute and get ouput into a variable in stored procedure

I generate a dynamic query in My sql Stored procedure. I wanna get the result of this query into a out parameter. How to do this ? CREATE PROCEDURE 'searchInvoice' ( OUT numOfRecords INT ) BEGIN DECLARE query1 TEXT; DECLARE query2 TEXT; SET …

Read More
Assign function result to a table variable

The SQL Server (2000/2005) function gets the table name and the field name as parameters and returns results from a dynamic query within the function. The results should be assigned to a Table variable which will be used further in a …

Read More
add column dynamically with select query

I have one table which has 20 columns default These 20 columns named as D1 D2 D3...D20 , now with select query i want to add the other columns dynamically.. for ex D21 D22...D31, so how can i write a query …

Read More