user92038111111
user92038111111

Reputation: 191

Passing a query as a parameter in stored proc

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

Answers (2)

Deepak Kumar
Deepak Kumar

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

Nick Pfitzner
Nick Pfitzner

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

Related Questions