Procedure expects parameter '@handle' of type 'int'.

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

Hello Friends,
I'm trying executing dynamic query using

EXEC SP_EXECUTE

but it gives error saying:
Procedure expects parameter '@handle' of type 'int'.

Here is my procedure

ALTER proc [dbo].[sp_StudioReleaseChallan_sel]  
(
	@Criteria nvarchar(4000)='', 
	@startRowIndex  int=0,    
	@maximumRows  int=0  )  
as  
begin  
set nocount on;  
DECLARE @SQL NVARCHAR(MAX)
IF LEN(@Criteria) <> 0
BEGIN
	SET @Criteria = ' AND ' + @Criteria
END

SET @SQL = 'select A.iterationid [jobid], jobno,firstname+'' ''+lastname [User],clientname,projectname,brandname,awcategoryname,  
 ''Studio Master'' [JobType]  
,variantname,quantity+'' ''+unit [SKU],iterationdate [StartTime],expecteddeleverydate,statusname  
,artworktype,A.statusid from tbl_iteration A  
inner join tbl_user U on U.userid=A.userid  
inner join tbl_client C on C.clientid = A.clientid  
inner join tbl_project P on P.projectid = A.projectid  
inner join tbl_brand B on B.brandid = A.brandid  
inner join tbl_awcategory AW on AW.awcategoryid=A.artworkcategoryid  
inner join tbl_variant V on V.variantid = A.variantid  
inner join tbl_sku on tbl_sku.skuid=A.skuid  
inner join tbl_status S on S.statusid = A.statusid  
where artworktype = 9 and A.statusid not in (30,45,49) '+ @CRITERIA + ' 
order by jobno desc'  

EXEC SP_EXECUTE @SQL
end

Can anyone tell what this error means

Thanks in advance

Posted 3-Jun-11 1:47am

dhage.prashant01

Updated 11-Oct-19 3:14am

v2

Add a Solution

Comments

Kiran Sonawane 3-Jun-11 7:55am

Provide us procedure
This could help you
http://forums.asp.net/t/1454533.aspx/1

dhage.prashant01 3-Jun-11 7:58am

I have added the Procedure in my question

dhage.prashant01 3-Jun-11 8:02am

its working i used
sp_executesql instead of sp_execute

4 solutions

Solution 3

You can do this:

SQL

exec(@sql)

or this:

SQL

exec sp_executesql @sql

Posted 3-Jun-11 2:15am

#realJSOP

Comments

phil.o 11-Oct-19 9:24am

Down-vote countered. 8 years later, finally :thumbsup:

Solution 2

Dear Prashant,

Please check the syntax of sp_executesql. following is the example to get row count from the table using dynamic query

DECLARE @SQLString nvarchar(500);
DECLARE @CountSQLQuery varchar(30);
DECLARE @CountResult varchar(30);

SET @SQLString = N'SELECT @result = COUNT(*) FROM TableName'
	
EXECUTE sp_executesql @SQLString, N'@result varchar(30) OUTPUT',@result=@CountSQLQuery OUTPUT;

Posted 3-Jun-11 2:00am

LaxmikantYadav

Solution 4

Hello,

You need to user exec SP_EXECUTESQL @sql. Since you are tring to execute dynamic query. sp_executesql is inbuilt stored procedure provided my microsoft which accepts a query in string format and executes the same against the databse.

There are two different way of executing the dynamic query.

1) without using inbuild Store procedure

exec (@sql)

2) Using stored procedure

exec SP_EXECUTESQL @sql

Posted 3-Jun-11 2:21am

HemantBPatil

Updated 3-Jun-11 2:22am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**R. Giskard Reventlov** · Accepted Answer · 2011-06-03T01:55:00

Solution 1

See here[^].

You should probably use:

exec sp_executesql MyProc

Posted 3-Jun-11 1:55am

R. Giskard Reventlov