Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    52

    Unanswered: Execute dinamic query statement in stored procedure

    Hi all friends:

    Check this stored procedure please:

    CREATE PROCEDURE mySp
    AS
    BEGIN
    DECLARE @strVar VARCHAR(200)

    SELECT @strVar = "SELECT COUNT(*) FROM myTable1"

    EXEC (@strVar)
    END


    it works ok, this SP gives as a result a number, but i want to compare this result in a condition statement, for example i want to write the next code, but it doesn't compile well.



    CREATE PROCEDURE mySp
    AS
    BEGIN
    DECLARE @strVar VARCHAR(200)

    SELECT @strVar = "SELECT COUNT(*) FROM myTable1"

    IF EXEC (@strVar) > 0
    BEGIN
    PRINT "HI, ALL IS FINE"
    END
    END

    I also tried the next, but is the same, it doesn´t compile:


    CREATE PROCEDURE mySp
    AS
    BEGIN
    DECLARE @strVar VARCHAR(200)
    DECLARE @myNum INT


    SELECT @strVar = "SELECT COUNT(*) FROM myTable1"

    SELECT @myNum = EXEC (@strVar)

    IF @myNum > 0
    BEGIN
    PRINT "HI, ALL IS FINE"
    END
    END



    Any ideas that how can i do this, i put the SELECT COUNT(*) query in a variable , because is a dinamyc statement query.



    Any help will be thanked



    Greetings!!!!!!

  2. #2
    Join Date
    Nov 2002
    Posts
    207
    Only way I can think of is ...

    SP1 with output as count(*)

    SP2 with input from SP1

    So, you have 2 SPs instead of one.

    HTH.

  3. #3
    Join Date
    Nov 2003
    Posts
    52
    Hi mkalsi i couldn´t understand what you meant,

    Could you be more clear please???



    ThanX!!!

  4. #4
    Join Date
    Nov 2002
    Posts
    207
    Create one procedure with output variable and use that variable in second procedure.

  5. #5
    Join Date
    Jun 2004
    Posts
    3

    Lightbulb workaround

    Hi,
    i tried the same thing and came to the following conclusion:
    You cannot give the result of a dynamic SQL-Statement back to the calling SP.
    I'm using the following workaround in my SPs:
    creating a tempTable in the SP,
    inserting the result of the dynamic statement into this temp table,
    reading the tempTable after the dynamic-statement into a local variable.

    Like this:

    declare @anz int
    declare @command varchar(255)

    create table #temp_table (d_value int null)

    select @command = "insert #temp_table select count(*) from myTable1"
    exec (@command)
    select @anz = d_value from #temp_table

    if (@anz > 0)
    ...

    HTH
    Bernd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •