Executing a Stored Proc w/ Parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Executing a Stored Proc w/ Parameters

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2003-09-05 : 14:59:47

I'm trying to execute a stored proc in the Query Analyzer window.

The stored proc has input and output paramters.

Here's the code:


Create table #results (partnumber varchar(50), pattern varchar(10))

Declare @partnumber varchar(50), 
	@result varchar(10)

Declare mycursor Cursor for 
		select * from #temp

Open mycursor
fetch next from mycursor into @partnumber 
while (@@fetch_status<>-1) 
	Begin 
	
		etech_portal.dbo.zz_partnumber_to_PatternCode @partnumber, @result
		insert into #results values (@partnumber, @result) 
	
	fetch next from mycursor into @partnumber 
	End 

deallocate mycursor
select * from #results

And here's the error:


Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near 'etech_portal'.
Server: Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'End'.

What am I doing wrong here? When I use normal values instead of variables when kicking off the Stored Proc, it works.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-09-05 : 15:03:10

might need to EXEC the stored procedure.

Jonathan
{0}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-05 : 15:09:50

Why are you using a cursor for this?

Does #temp only have only column?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-05 : 15:10:44

Don't you need to specify OUTPUT?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

label
Posting Yak Master

197 Posts

Posted - 2003-09-05 : 15:15:52

quote:
Originally posted by setbasedisthetruepath

might need to EXEC the stored procedure.

Jonathan
{0}

Yeah, I thought the same thing and tried that but got the same error.

label
Posting Yak Master

197 Posts

Posted - 2003-09-05 : 15:17:58

quote:
Originally posted by tduggan

Why are you using a cursor for this?

Does #temp only have only column?

Tara

The first several rows from the #temp table look like this:


Partnumber					Sales
--------------					---------------
ULUSP-99003                             	4145411.2170
INR-498-001B                            	1875873.3280
NAS2201F-N01-07S                        	1193494.2150
INR-498-003B                            	1066821.1000
XGT-0402AWM-X16                         	1065823.1840
INR-498-012C                            	1018247.9000
IT1011-N31-X4                           	1016469.3490
XGT-0101AWM-X16                         	969399.4250
KQ2L07-34S                              	967501.5080
INR-ULT000009                           	929850.0410
NVFS2100-5FZ                            	910396.1640
MHZL2-20C-X3074                         	906387.1810
M990606                                 	854964.5280
VV829-04S-SUQW06BT-W1                   	842274.8710
US1754                                  	824832.8080

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-05 : 15:18:59

I don't see where @result is being set. Does #temp have this value because you aren't putting the value into a variable in the cursor?

Tara

label
Posting Yak Master

197 Posts

Posted - 2003-09-05 : 15:19:01

quote:
Originally posted by X002548

Don't you need to specify OUTPUT?

I tried that and it told me that wasn't a proper word to use with "Declare".

Apparently, you can only make a variable an "OUTPUT" type in the first part of a Stored proc.

label
Posting Yak Master

197 Posts

Posted - 2003-09-05 : 15:21:01

quote:
Originally posted by tduggan

I don't see where @result is being set. Does #temp have this value because you aren't putting the value into a variable in the cursor?

The @result would hopefully be set because the second paramter of the stored procedure I'm trying to run is an output parameter.

The #temp table is a very slimmed down bit of data from a much larger table. I just wanted to keep the data in a #temp table while I was testing because it takes 7 minutes to populate the cursor every time I run it. This way, I just set the cursor to run off the #temp table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-05 : 15:21:33

You aren't doing the cursor thing correctly. You have two columns in the table, but you are only using one variable in the cursor. You also should not use select *. If you just need the one column, then SELCT Partnumber FROM #temp.

You also don't put OUTPUT in the DECLARE statement. You put it like this:

etech_portal.dbo.zz_partnumber_to_PatternCode @partnumber, @result OUTPUT

Tara

label
Posting Yak Master

197 Posts

Posted - 2003-09-05 : 15:25:55

quote:
Originally posted by tduggan

You aren't doing the cursor thing correctly. You have two columns in the table, but you are only using one variable in the cursor. You also should not use select *.

Yep, I was writing it in a hurry and missed that. I've changed it to


Declare mycursor Cursor for 
		select partnumber from #temp

quote:
You also don't put OUTPUT in the DECLARE statement. You put it like this:

etech_portal.dbo.zz_partnumber_to_PatternCode @partnumber, @result OUTPUT

Cool. I've adjusted it accordingly. However, I'm still getting those syntax errors:


Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near 'etech_portal'.
Server: Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'End'.

My code now looks like:


Create table #results (partnumber varchar(50), pattern varchar(10))

Declare @partnumber varchar(50), 
	@result varchar(10)

Declare mycursor Cursor for 
		select partnumber from #temp

Open mycursor
fetch next from mycursor into @partnumber 
while (@@fetch_status<>-1) 
	Begin 
	
		etech_portal.dbo.zz_partnumber_to_PatternCode @partnumber, @result output
		insert into #results values (@partnumber, @result) 
	
	fetch next from mycursor into @partnumber 
	End 

deallocate mycursor
select * from #results

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-05 : 15:29:42

I just copied your code into Query Analyzer and I get the same error.

But when I add EXEC like Jonathan mentioned, it works fine.


Create table #results 
(
partnumber varchar(50), 
pattern varchar(10)
)

Declare @partnumber varchar(50), @result varchar(10)

Declare mycursor Cursor for 
		select Partnumber from #temp

Open mycursor
fetch next from mycursor into @partnumber 

while (@@fetch_status<>-1) 
Begin 
	
	EXEC etech_portal.dbo.zz_partnumber_to_PatternCode @partnumber, @result OUTPUT
	insert into #results values (@partnumber, @result) 
	
	fetch next from mycursor into @partnumber 
End 

deallocate mycursor

select * from #results

Tara

label
Posting Yak Master

197 Posts

Posted - 2003-09-05 : 15:42:51

quote:
Originally posted by tduggan

I just copied your code into Query Analyzer and I get the same error.

But when I add EXEC like Jonathan mentioned, it works fine.
Tara

Hmmmm....worked for me that time too. I must have not implemented properly before.

The only problem now I have is that the output variable is not being populated.....so I'm not getting any return value populated into the #results table.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-05 : 15:45:00

Jon,

How big is the sproc?

Can you post it?

$1,000.00 we can turn that cursor inside out...

(in otherwords lose it...)

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-05 : 15:51:41

zz_partnumber_to_PatternCode needs to know that @result is an output parameter.

Like this:

CREATE zz_partnumber_to_PatternCode
(@Partnumber VARCHAR(50), @Result VARCHAR(10) OUTPUT
AS
...

Tara

label
Posting Yak Master

197 Posts

Posted - 2003-09-05 : 16:07:21

quote:
Originally posted by tduggan

zz_partnumber_to_PatternCode needs to know that @result is an output parameter.

Like this:

CREATE zz_partnumber_to_PatternCode
(@Partnumber VARCHAR(50), @Result VARCHAR(10) OUTPUT
AS
...

Tara

I believe that's how it's set up.....


CREATE PROCEDURE dbo.zz_partnumber_to_PatternCode 

@partnumber varchar(50), 
@returnpattern varchar(10) output

AS

Begin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-05 : 16:09:45

Put a PRINT statement in between the EXEC and the INSERT:

EXEC etech_portal.dbo.zz_partnumber_to_PatternCode @partnumber, @result OUTPUT

PRINT @result

insert into #results values (@partnumber, @result)

You might also want to put a PRINT statement for @partnumber before the EXEC to make sure that you have the correct values. Do both PRINT statements give you what you expect?

Tara

label
Posting Yak Master

197 Posts

Posted - 2003-09-05 : 16:32:40

quote:
Originally posted by tduggan

Put a PRINT statement in between the EXEC and the INSERT:

You might also want to put a PRINT statement for @partnumber before the EXEC to make sure that you have the correct values. Do both PRINT statements give you what you expect?

I put the following lines in:


		Exec etech_portal.dbo.zz_partnumber_to_PatternCode @partnumber, @result output
		PRINT 'My part = ' + @partnumber
		PRINT 'My pattern = ' + @result
		insert into #results values (@partnumber, @result)

I get the 'My part = ' and the partnumber just fine.

The 'my pattern' never prints even that text, much less the value I'm supposed to be getting back.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-05 : 16:33:59

@result must be NULL.

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-05 : 16:36:23

Your stored procedure must be incorrect then because it is setting @result to NULL as Jeff mentioned. You will need to troubleshoot that or post the code for it for us to take a look.

Tara

label
Posting Yak Master

197 Posts

Posted - 2003-09-05 : 17:06:30

quote:
Originally posted by tduggan

Your stored procedure must be incorrect then because it is setting @result to NULL as Jeff mentioned. You will need to troubleshoot that or post the code for it for us to take a look.

The result is null some times. That's an expected result in some instances.

When I plug these numbers in manually:


Partnumber					Sales
--------------					---------------
ULUSP-99003                             	4145411.2170
INR-498-001B                            	1875873.3280
NAS2201F-N01-07S                        	1193494.2150
INR-498-003B                            	1066821.1000
XGT-0402AWM-X16                         	1065823.1840
INR-498-012C                            	1018247.9000
IT1011-N31-X4                           	1016469.3490
XGT-0101AWM-X16                         	969399.4250
KQ2L07-34S                              	967501.5080
INR-ULT000009                           	929850.0410
NVFS2100-5FZ                            	910396.1640
MHZL2-20C-X3074                         	906387.1810
M990606                                 	854964.5280
VV829-04S-SUQW06BT-W1                   	842274.8710
US1754                                  	824832.8080

The first 8 Partnumbers don't have a match, but the 9th one 'KQ2L07-34S' does and when I just run the stored proc like this:


etech_portal.dbo.zz_partnumber_to_PatternCode 'KQ2L07-34S', ''

it returns the pattern code just fine.

The stored proc is functioning just fine when I run it with the values manually typed in.....for some reason it's just not returning the value when I perform the exec.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources