Stored Procedure Parameters - SQL Server Forums

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)

Stored Procedure Parameters

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-07-11 : 06:24:39

George writes "I have the following stored procedure which works fine, however I am trying to get a parameter like check number to restrict in the where clause. can someone help?

--exec SP_PrintLienWaiver '136786'

ALTER Procedure SP_PrintLienWaiver
as
declare @Check_Number varchar(6)
set @check_number= @check_number + '%'
select * FROM JC20705
AS J
JOIN
PM30200 AS CK ON J.VCHRNMBR=CK.VCHRNMBR
and j.VENDORID=ck.VENDORID
and j.BACHNUMB=CK.BAChNUMB
join
pm30300 as RC on J.VCHRNMBR=RC.VCHRNMBR
join
pop10310 as PO on j.ws_Job_Number=po.JOBNUMBR
where
@Check_Number=CHEKNMBR"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-07-11 : 06:27:05

cheknmbr like @check_number

quote:
Originally posted by AskSQLTeam

where
@Check_Number=CHEKNMBR"

--------------------
keeping it simple...

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-07-11 : 08:29:55

will that proc even run? there is no parameter defined, yet you pass one in. Is this what you meant?


ALTER PROCEDURE dbo.SP_PrintLienWaiver 
(	
	@Check_Number VARCHAR(6)
)

AS

SET @check_number= @check_number + '%'
SELECT * FROM JC20705
AS J
JOIN
PM30200 AS CK ON J.VCHRNMBR=CK.VCHRNMBR
and j.VENDORID=ck.VENDORID
and j.BACHNUMB=CK.BAChNUMB
join
pm30300 as RC on J.VCHRNMBR=RC.VCHRNMBR
join
pop10310 as PO on j.ws_Job_Number=po.JOBNUMBR
where
CHEKNMBR like @Check_Number

Select * is crap. Use a column list instead.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources