Creating Stored Procedure with SELECT ... inside

  • 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 @onhanddbo.pqty,

    @onorderdbo.pqty,

    @onpicklistdbo.pqty,

    @zsvDFQdbo.pqty,

    @zsvShp_in_DFQbit

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

    SELECTp.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

  • Select the values into #table and work with #table

  • I can't use an INTO clause on the select statement. How to I get it into #table?

  • 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[/url]

  • 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
  • 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

  • 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

  • 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. 😉

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply