CODEWITHSUNDEEP
sql-serverdatabasesql-server-2012
Rajaram Shelar
Rajaram Shelar

Reputation: 7877

Execute sql stored procedure by passing parameters as a query

I tried many time to pass query as parameter to the execute a stored procedure. But it is giving compile time error everytime. Below is my query

exec IAM_INSERT_ASSET (select top 1 ItemID from MIR_ItemMaster order by ItemID desc),1662

There are two parameters, out of which first is dynamic by the query and second is hard coded. Obviously I can do this by keeping query in variable and passing variable. But I do not want to declare variable and pass query directly to the procedure. How can this be achieved. Any help?

Upvotes: 1

Views: 27125

Answers (5)

Bedabrata
Bedabrata

Reputation: 15

@eraj
I get your basic query.
However, I don't think it can be done exactly the way you want to, i.e. passing the query as a parameter. This can be done in two ways I feel.

1) Write a cursor with this SELECT and Execute the SP from within the cursor. 

DECLARE @p1 varchar(800);
DECLARE @p2 varchar(800);
DECLARE @p3 varchar(800);

DECLARE SP_Cursor CURSOR
    FOR SELECT p1,p2,p3
        FROM ..

OPEN SP_Cursor;

FETCH NEXT FROM SP_Cursor INTO @p1, @p2, @p3;

WHILE (@@FETCH_STATUS <> -1)  
BEGIN  
    EXEC SP_Name @p1,@p2,@p3;  
    FETCH NEXT FROM SP_Cursor INTO @p1, @p2, @p3;  
END  

CLOSE SP_Cursor;

DEALLOCATE SP_Cursor;

As I said, this would be slow as hell since if your query returns 1000 rows, the SP would execute 1000 times connecting to the DB every time.
You can improve the performance somewhat by putting the data set into a #temp_table and looping through a WHILE loop. It would at least cut out on connecting to the DB each time.

2) The second option is to build the execution statement itself through a SELECT query. 

declare @execstatementsbatch nvarchar(max)
select @execstatementsbatch = ''

SELECT @execstatementsbatch = @execstatementsbatch   + 'EXEC SP_Name' + p1 +  ', '  + p2 +  ', '  + p3 + '; ' 
FROM..
<some conditions>

exec(@execstatementsbatch)

But mind you, both are not very good for performance in case you're working with huge amount of data.

Upvotes: 1

brian
brian

Reputation: 1

Maybe this will work?

declare @docnumber2 char(21) -- or whatever the field is varchar,int,etc.
set @docnumber2 = (select top 1 docnumber from invoices order by doc_timestamp)
exec udsp_blahblah @docnumber2

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239654

All1 that you can supply in an EXEC statement are literal values or variables. Here's the documented syntax for EXEC:

[ { EXEC | EXECUTE } ]
    { 
      [ @return_status = ]
      { module_name [ ;number ] | @module_name_var } 
        [ [ @parameter = ] { value 
                           | @variable [ OUTPUT ] 
                           | [ DEFAULT ] 
                           }
        ]
      [ ,...n ]
      [ WITH <execute_option> [ ,...n ] ]
    }
[;]

That's it. So you'll have to go the variable route.

1 You can also use a few of the built in functions, like @@IDENTITY, which used to be a "global variable", but it's very much the exception rather than the rule.

Upvotes: 1

Neville Kuyt
Neville Kuyt

Reputation: 29629

Firstly, I haven't got access to SQL Server right now, so this may not work.

Secondly, I don't understand why you don't want to use a variable - it's by far the simplest solution. If you're building your stored procedures to be too clever - e.g. have IAM_INSERT_ASSET behave differently depending on the first parameter's values, I'd recommend you reconsider that. Stored procedures are hard to test, hard to debug, and hard to understand from an architectural point of view, so you often end up breaking a stored procedure through an apparently unrelated change (e.g. adding a column to a table).

Finally, if you really want to do this, you can use dynamic SQL. If you change the first parameter to be a varchar, and then use sp_executeSQL to execute the contents of that varchar, you can pass in whatever you want. Caveat - I don't know if this works, can't try it. Other caveat - this may make your system brittle and fail in exciting ways.

Upvotes: 0

Bedabrata
Bedabrata

Reputation: 15

Why not define the first parameter as varchar and pass the query as a string? It would basically look like,

exec IAM_INSERT_ASSET 'select top 1 ItemID from MIR_ItemMaster order by ItemID desc',1662

Upvotes: 0

Related Questions