Stored Procedure - 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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-11-08 : 04:07:00

Guys,

Can you help me to do this script and run in Stored procedure.
actually, i did not make any SP scripts my from work.
this is my first time to try.

Here is the script.

SELECT r.PURCHPOOLID, r.CREATEDDATETIME, p.DELIVERYDATE, p.PURCHID, p.LINENUM, r.ORDERACCOUNT, p.ITEMID, p.NAME, p.QTYORDERED,
p.PURCHRECEIVEDNOW, p.REMAINPURCHPHYSICAL, p.PURCHPRICE, p.LINEAMOUNT, r.VENDORREF, r.INVENTLOCATIONID
FROM PURCHLINE AS p WITH (nolock) LEFT OUTER JOIN
PURCHTABLE AS r WITH (nolock) ON p.PURCHID = r.PURCHID
WHERE (r.CREATEDDATETIME <= @CreatedDatetime) AND (p.PURCHSTATUS = @Purchstatus) AND (r.PURCHPOOLID = @Purchpoolid) AND
(r.INVENTLOCATIONID IN (@inventlocationid))

Thank you in advance.

JOV

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-08 : 04:18:08

create proc myproc
@CreatedDatetime datetime ,
@Purchstatus varchar(10) ,
@Purchpoolid int ,
@inventlocationid varchar(100)
as
SELECT r.PURCHPOOLID, r.CREATEDDATETIME, p.DELIVERYDATE, p.PURCHID, p.LINENUM, r.ORDERACCOUNT, p.ITEMID, p.NAME, p.QTYORDERED,
p.PURCHRECEIVEDNOW, p.REMAINPURCHPHYSICAL, p.PURCHPRICE, p.LINEAMOUNT, r.VENDORREF, r.INVENTLOCATIONID
FROM PURCHLINE AS p WITH (nolock) LEFT OUTER JOIN
PURCHTABLE AS r WITH (nolock) ON p.PURCHID = r.PURCHID
WHERE r.CREATEDDATETIME <= @CreatedDatetime
AND p.PURCHSTATUS = @Purchstatus
AND r.PURCHPOOLID = @Purchpoolid
AND ',' + @inventlocationid + ',' like '%,' + convert(varchar(20),r.INVENTLOCATIONID) + ',%'
go

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2011-11-08 : 20:11:40

Hi NigelRivett,

what is the difference between this two:
1. (r.INVENTLOCATIONID IN (@inventlocationid))
and
2.',' + @inventlocationid + ',' like '%,' + convert(varchar(20),r.INVENTLOCATIONID) + ',%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 01:04:30

quote:
Originally posted by Villanuev

Hi NigelRivett,

what is the difference between this two:
1. (r.INVENTLOCATIONID IN (@inventlocationid))
and
2.',' + @inventlocationid + ',' like '%,' + convert(varchar(20),r.INVENTLOCATIONID) + ',%'

in case of 1, passing group of value through a variable like this in IN operator wont parse and check them for each of value . it tries to find out a single value which variable contains (including the delimiter)

ie suppose you're passing value of variable as 1,2,3,4,5 to get all records matching all ids (1-5) .Using above logic involving IN operator interprets them as single value and will look for single value 1,2,3,4,5 in field which it never finds

in second you're looking for pattern of ,id, inside the comma seperated string ,1,2,3,4,5, (see ' appended before and after). this will cause it to match for each value within and you get record for all these ids

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources