How to execute 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

SQL Server 2005 Forums

Transact-SQL (2005)

How to execute procedure ??

Author

Topic

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-04-16 : 09:22:44

Guys,

I want to pass a string to a procedure but don't know how to go about that..

IN the below proc, i want to pass:
@AssetGrupName = 'TESTING'
@SerialNumber = '123AFSDF','23423ASDF','343234ASD'

How to execute this proc now?

This way i tried:
AssetToAssetGroup 'TESTING', ''123AFSDF','23423ASDF','343234ASD''

But it doesn't work..

Procedure body:

ALTER PROCEDURE AssetToAssetGroup
@AssetGrupName VarChar(15),
@SerialNumber VarChar(500)
AS
Declare
@AssetUid Int,
@AssetGroupUid Int,
@CompanyUid Int,
@RowNum Int,
@count int

BEGIN

Create table #tempTest
(
AssetGroupUid varchar,
AssetUid varchar,
AssetGroupAssetUid int
)

If Not Exists (Select * From assetGroup Where groupname = @AssetGrupName)
BEGIN
print 1
Return
END

Select @CompanyUid=CompanyUid, @AssetGroupUid=AssetGroupUid From assetGroup
where groupname = @AssetGrupName

If Not Exists (Select * From Asset Where CompanyUid=@CompanyUid and DeviceSerialNumber in (@SerialNumber))
BEGIN
print 2
Return
END

Declare Cur_GetUid CURSOR FOR
Select Uid, row_number() over (order by Uid ) As RowNumber
From Asset Where CompanyUid=@CompanyUid and DeviceSerialNumber in (@SerialNumber)

Open Cur_GetUid

Fetch Next From Cur_GetUid Into @AssetUid, @RowNum;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @@FETCH_STATUS <> -2
BEGIN

If Not Exists (Select * From AssetGroupAsset
Where AssetGroupUid=@AssetGroupUid And AssetUid=@AssetUid)

Insert #tempTest (AssetGroupUid, AssetUid, AssetGroupAssetUid)
Values (@AssetGroupUid, @AssetUid, @RowNum)
print 3
Fetch Next From Cur_GetUid Into @AssetUid, @RowNum;

END
Close Cur_GetUid
DeAllocate Cur_GetUid

Select * from #TempTest
END

END

Guys, please help out..
Thanks,
Sourav

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-16 : 09:31:09

Firstly, you do not need a cursor to do this, all you are going to do is slow down the procedure. Secondly, do a search on this site for a PARSE function as you need this to split your results to be read by your in clause.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-16 : 09:36:54

or search for Array+SQL Server in google

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources