Pass Parameters to SProc using SELECT

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)

Pass Parameters to SProc using SELECT

Author

Topic

pyu.agrawal
Starting Member

29 Posts

Posted - 2009-05-31 : 01:04:08

Hi,
How can I pass parameters to a SProc, using a SELECT?
The parameters passed would be selected from a table

Thanks

nalnait
Starting Member

14 Posts

Posted - 2009-05-31 : 01:42:59

CREATE PROC TEST_SP
(
@tablename varchar(50)
)
AS
BEGIN

EXEC ('SELECT * FROM '+@tablename+'')
END

EXEC TEST_SP 'test'
--?

a.rameshk
Starting Member

19 Posts

Posted - 2009-05-31 : 03:36:15

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees
@LastName nvarchar(50),
@FirstName nvarchar(50)
AS
SELECT FirstName, LastName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment
WHERE FirstName = @FirstName AND LastName = @LastName;
GO

---Extracting fom BOL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-31 : 06:35:27

do you mean this?

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

pyu.agrawal
Starting Member

29 Posts

Posted - 2009-05-31 : 08:08:07

Hi visakh16,
What exactly I'm looking for: I want to execute this sproc spNewUserData

EXEC dbo.spNewUserData
@KeyData = @Value1,
@CategoryID = @Value2,
@Source = @Value3,
@SMSStatusID = @Value4,
@Remark = @Value5

The values [value1 to value5 should be selected from a table]. I want to copy data from one table to another using the sproc spNewUserData

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-31 : 14:12:16

so you want to execute the sp for each record of a table? in that case, you need a cursor or while loop.if thats the case why dont you make it a function instead. what is the stored procedure currently doing? can you give some info on logic used instead?

pyu.agrawal
Starting Member

29 Posts

Posted - 2009-05-31 : 14:40:59

Can you shed some light on how could I use cursor/while loop?.
The SProc basically does an insert on three tables.

-- Data Management Criteria: Update Old From Latest
IF (@Criteria = 102)
BEGIN
IF
(
NOT EXISTS (
SELECT ID FROM UserData WHERE
KeyData = @KeyData
)
)
BEGIN
INSERT INTO UserData (KeyData, SourceID, SMSStatusID, Remark, Remark1, AddedOn, UserID)
VALUES (@KeyData, @SourceID, @SMSStatusID, @Remark, @Remark1, @AddedOn, @UserID)
END
ELSE
BEGIN
UPDATE UserData SET
SourceID = @SourceID,
SMSStatusID = @SMSStatusID,
Remark = @Remark,
Remark1 = @Remark1,
UserID = @UserID
WHERE
KeyData = @KeyData
END
END

-- Data Management Criteria: Remove Old Keep Latest
IF (@Criteria = 103)
BEGIN
IF
(
EXISTS (
SELECT ID FROM UserData WHERE
KeyData = @KeyData
)
)
BEGIN
DELETE FROM UserData WHERE
KeyData = @KeyData
INSERT INTO UserData (KeyData, SourceID, SMSStatusID, Remark, Remark1, AddedOn, UserID)
VALUES (@KeyData, @SourceID, @SMSStatusID, @Remark, @Remark1, @AddedOn, @UserID)
END
END

This is just a part of the SProc.

Peeyush Agrawal

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-31 : 18:47:13

Since your data is coming directly from a table anyways, why not just use it in the stored procedure.



INSERT INTO UserData (KeyData, SourceID, SMSStatusID, Remark, Remark1, AddedOn, UserID)
select KeyData, SourceID, SMSStatusID, Remark, Remark1, AddedOn, UserID from
Mytable a 
where not exists ( select * from UserData aa where aa.Keydata = a.Keydata)
and a.Criteria = 102


UPDATE a
SET
a.SourceID = b.SourceID,
a.SMSStatusID = b.SMSStatusID,
a.Remark = b.Remark,
a.Remark1 = b.Remark1,
a.UserID = b.UserID
from Userdata a
inner join
Mytable b
on a.Keydata = b.Keydata
and b.Criteria = 102

delete a from userdata a inner join mytable b on a.Keydata = b.Keydata where b.Criteria = 103

INSERT INTO UserData (KeyData, SourceID, SMSStatusID, Remark, Remark1, AddedOn, UserID)
select KeyData, SourceID, SMSStatusID, Remark, Remark1, AddedOn, UserID from
Mytable a 
where not exists ( select * from UserData aa where aa.Keydata = a.Keydata)
and a.Criteria = 103

This method will be much more efficiant for you.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources