SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating Stored Procedure with SELECT ... inside


Creating Stored Procedure with SELECT ... inside

Author
Message
mike 57299
mike 57299
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 507
Here is my stored procedure so far:

IF OBJECT_ID('dbo.usp_shipability') IS NOT NULL
DROP PROCEDURE dbo.usp_shipability
GO

CREATE PROCEDURE dbo.usp_shipability (@product_id AS int=0, @qty AS dbo.pqty=0, @nReturn dbo.percentage=0 OUT)
AS
DECLARE @onhand dbo.pqty,
@onorder dbo.pqty,
@onpicklist dbo.pqty,
@zsvDFQ dbo.pqty,
@zsvShp_in_DFQ bit

IF @product_id = 0 OR @product_id IS NULL GOTO EarlyOut

SELECT p.p_nmbr,
ps.onhand,
ps.onorder,
ps.onpicklist,
iss.df_qty AS zsvDFQ,
c.ship_in_def_qty_fl AS zsvShip_in_DFQ
FROM product p
inner join product_Stat ps ON p.product_id = ps.product_id
INNER JOIN inventory_spec iss on p.product_id = iss.product_id
inner join company c on c.company_id = p.company_id
WHERE p.product_id = @product_id
--INTO cursor #test

SET @nReturn = query.zsvDFQ

*****
How do I get the values from the select statement into vars that can be work with so that I can return a single amount?

If I put the "into cursor #test", it tells me illegal word near into.

Any ideas would be great.

Thanks,
Mike
Chandra Sekhara Vyas Dhara
Chandra Sekhara Vyas Dhara
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4213 Visits: 1149
Select the values into #table and work with #table



mike 57299
mike 57299
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 507
I can't use an INTO clause on the select statement. How to I get it into #table?
wschampheleer
wschampheleer
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1036 Visits: 303
To get the syntax correct, you should remove the word cursor and move the line starting with INTO before the from statement, similar to:

SELECT columns
INTO #temptable --!
FROM mytable
WHERE 1 = 0;

But, if I understand correctly what you are trying to achieve, you don't need a cursor or a temp table. Try this:

CREATE PROCEDURE tmp
@col1 INT OUTPUT
,@col2 VARCHAR(100) OUTPUT
AS
SET NOCOUNT ON ;

SELECT @col1 = col1
,@col2 = col2
FROM mytable
WHERE ...;

To test it, run the following script:

DECLARE @var1 INT
,@var2 VARCHAR(100) ;
EXEC tmp
@var1 OUTPUT
,@var2 OUTPUT ;
SELECT @var1
,@var2 ;

An alternative to using output variables that will also work if you want to return values from more than 1 record is:

CREATE PROCEDURE tmp
AS
SET NOCOUNT ON ;

SELECT col1, col2
FROM mytable
WHERE ...;

And for the calling part:

CREATE TABLE #tmp (
col1 INT
,col2 VARCHAR(100)) ;

INSERT INTO #tmp
EXEC tmp ;

SELECT *
FROM #tmp ;

Regards,

Willem
http://wschampheleer.wordpress.com
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88667 Visits: 45284
mike 57299 (1/27/2010)
How do I get the values from the select statement into vars that can be work with so that I can return a single amount?


What are you trying to do here/ I'm asking about the actual problem that you're trying to solve, as there may be another way to go about it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


mike 57299
mike 57299
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 507
What I am trying to accomplish is the following:

Input: product ID
Output: calculated value

Process:
Use input to look up up product information distributed across 3 tables. Do some calculations. The calculations create one final number. Return that number.

I would love to have this as a function so that I can do something like the following:

select Product.product, ufn_ship( product.product_id) AS shipnum, ....

or @xyz = ufn_ship( 1234)

Thanks for any help.

Mike
Florian Reischl
Florian Reischl
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3583 Visits: 3934
Hi

Guess you are looking for a scalar function, not a procedure. This enables you to directly return a value into a scalar variable.


-- scalar function
DROP FUNCTION GetAmount;
GO
CREATE FUNCTION GetAmount(@productId INT)
RETURNS DECIMAL(15,5)
AS
BEGIN
RETURN 1.234;
END
GO

DECLARE @amount DECIMAL(15,5);
SELECT @amount = dbo.GetAmount(123);
PRINT @amount;
GO



ATTENTION
Do NOT use those kind of scalar functions within a SELECT statement that returns much rows. The function will be called for each row.

To get data from a SELECT statement into a variable try this.

-- get SELECT into a variable
DECLARE @table_name NVARCHAR(128);

SELECT TOP(1)
@table_name = TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES;

PRINT @table_name;



Greets
Flo


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Paul White
Paul White
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16018 Visits: 11355
If you are looking to simulate a computed column across three tables (and it sounds as if you are), consider a view:


USE tempdb;
GO
-- Three related tables
CREATE TABLE dbo.T1 (PK INT IDENTITY PRIMARY KEY, A INT NULL);
CREATE TABLE dbo.T2 (PK INT IDENTITY PRIMARY KEY, B INT NULL);
CREATE TABLE dbo.T3 (PK INT IDENTITY PRIMARY KEY, C INT NULL);
GO
-- Sample data
INSERT dbo.T1 (A) VALUES (1), (2), (3);
INSERT dbo.T2 (B) VALUES (-4), (-5), (-6);
INSERT dbo.T3 (C) VALUES (14), (28), (36);
GO
-- View to create a sort of 'cross-table' computed column
-- Index the view if appropriate, to materialize the 'computed column'
CREATE VIEW dbo.V
WITH SCHEMABINDING
AS
SELECT T1.PK,
Value = T1.A * PI() + T2.B + LOG10(T3.C)
FROM dbo.T1
JOIN dbo.T2 ON T2.PK = T1.PK
JOIN dbo.T3 ON T3.PK = T2.PK;
GO
-- Show the contents of the view
SELECT PK, Value
FROM dbo.V;
GO
-- Tidy up
DROP VIEW dbo.V;
DROP TABLE dbo.T1, dbo.T2, dbo.T3;



Do not be tempted to use a T-SQL UDF to access data. Bad things will happen to you. ;-)



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search