Reputation: 191
I have a stored proc that takes in nvarchar
but it is going to part of the query as shown below:
Input Parameter @InputQuery = '21 OR RevenueAccrualID = 22'
I want to add the input query to my existing query:
UPDATE [RevenueAccrual] SET Posted=1
Where [RevenueAccrual].RevenueAccrualID = @InputQueryPart
The output I want is to be as:
UPDATE [RevenueAccrual] SET Posted=1
Where [RevenueAccrual].RevenueAccrualID = 21
OR RevenueAccrualID = 22
What it does at the moment is:
UPDATE [RevenueAccrual] SET Posted=1
Where [RevenueAccrual].RevenueAccrualID = '21 OR RevenueAccrualID = 22'
I want to know if there is a way to pass a query as a parameter and use it in SQL Server. The input query will differ depending on the situation.
Upvotes: 3
Views: 13338
Reputation: 668
Why you are passing the query as a parameter. This is very bad way of programming. Just pass the variables and write query is sp.
Passing query to sp is not an optimized way.
Upvotes: 1
Reputation: 216
You need to encapsulate the entire query in the string - so if you're passing @InputQuery into a stored procedure, then it should look something like this:
DECLARE @QueryString NVARCHAR(500)
SET @QueryString = 'UPDATE [RevenueAccrual] SET Posted=1 Where [RevenueAccrual].RevenueAccrualID = '
SET @QueryString = @QueryString + @InputQuery
EXEC (@QueryString)
So it can execute the entire thing at once. However, I'd probably change that approach if its always going to be integer values in the same field using IN
DECLARE @InputQuery NVARCHAR(30)
SET @InputQuery = '21, 22'
EXEC RunThisThang(@InputQuery)
Then your stored procedure looks like this:
CREATE PROCEDURE RunThisThang (@InputStr)
AS
DECLARE @QUeryStr NVARCHAR(500)
SET @QueryStr = 'UPDATE [RevenueAccrual] SET Posted=1 Where [RevenueAccrual].RevenueAccrualID IN (' + @InputStr + ')'
EXEC (@QueryStr)
Upvotes: 7