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

murva
Starting Member

3 Posts

Posted - 2011-01-07 : 10:28:06

I am trying to write a stored procedure with a parameter taken from another database.Here is the basic code


Create PROCEDURE
@myNumber char
as

set @myNumber='SELECT FROM SQLServer.dbo.Workers where myNumber="45635"'

Insert into dbo.tblEmployee (myNumber) values (@myNumber)

The procedure was created successfully, but when I try to execute it, it asks for the parameter myNumber. The idea here is that this parameter is taken from the select.
How could I fix this?Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-07 : 10:45:24

Do you want to pass the parameter to the stored procedure or no?

Without passing:

Create PROCEDURE <procname>
AS

DECLARE @myNumber char(<size goes here>)

set @myNumber = (SELECT <columnName> FROM SQLServer.dbo.Workers where myNumber = '45635')

Insert into dbo.tblEmployee (myNumber) values (@myNumber)

murva
Starting Member

3 Posts

Posted - 2011-01-07 : 11:03:21

Thanks, it really helped me. I have another question, do I have to open a new topic for that? I need now to pass more than one value. Can I put it in one single variable? In other words, a select containing more than 1 parameter(select myNumber,Grade) and put this single variable on the insert statement? Thanks again

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-07 : 13:15:03

yes if you want you can do it via following:

DECLARE @myNumber Datatyp [lengthofvariable_if varchar]
DECLARE @grade Datatyp [lengthofvariable_if varchar]

SELECT @myNumber=<columnName>,@grade=<ColumnName> FROM SQLServer.dbo.Workers where myNumber = '45635'

However note that you can assign only one value to a variable at a time ... e.g. you select statement must return one row as per given condition

Cheers!
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-08 : 01:13:23

if you want to store multiple values into variable you need to make varchar long enough and then create a comma separated list of values using query and assign it to variable. Alternatively if you need to store a result set you can go with table variable.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources