Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2006
    Posts
    9

    Unhappy Unanswered: Dynamic SQL (SP)

    Hello,

    I've tried to convert a static SQL to dynamic SQL but I'm always getting a NULL value

    My SP is:

    Code:
    CREATE PROCEDURE usp_dataList
    
    @text nvarchar(100)
    
    AS
    
    DECLARE
    @list varchar(8000),
    @SQL varchar(600)
    
    SELECT @SQL = 'SELECT '+ @list +'='+COALESCE(@list + ',', '') + 'CAST(data_received.data_data AS varchar(15)) 
    FROM data_received 
    WHERE data_received.data_data LIKE N'''+Replace(@text,' ',' OR data_received.data_data LIKE N''')+''''
    exec(@sql)
    
    SELECT @list AS data_list
    
    GO
    What's wrong here?
    I've tried to search google and find the problem my self in the past 3 hours but I couldn't find anything wrong (I'm new in SP)


    I also tried to change to this:
    Code:
    SELECT @SQL = 'SELECT '+ isnull(@list, '') +'='+COALESCE(@list + ',', '') + 'CAST(data_received.data_data AS varchar(15))
    I'm getting this error:
    Code:
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near '='.
    Please help me...

    Thanks,
    BuildHome

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    trying doing a print on your sql string instead of executing.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2006
    Posts
    9
    I'm getting this string:
    Code:
    SELECT =CAST(data_received.data_data AS varchar(15)) 
    	FROM data_received 
    	WHERE data_received.data_data LIKE N'test'
    I think that the "SELECT =CAST" isn't so good... :\

    How can I fix it?

    Thank you,
    BuildHome

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Get rid of the "=" and just use "SELECT CAST(....."
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2006
    Posts
    9
    Ok, but something is weird.
    I need the records to be separated with "," and not as rows... :\
    How can I do that?

  6. #6
    Join Date
    Feb 2006
    Posts
    9
    Ok, I used ASP to separate the records.

    Thanks for the helpers

  7. #7
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    You can use the Microsoft stored procedure sp_executesql with parameters and for what you are trying to achieve you need to use the output keyword as follows:

    DECLARE @outsidevar int,
    @sql nvarchar(4000)

    SET @outsidevar = 0
    SET @sql = N'select @insidevar = count(*) from int_jpa_transfer_details'
    EXEC sp_executesql @SQL, N'@insidevar int OUTPUT', @outsidevar OUTPUT
    select @outsidevar

    After you run this the @outsidevar variable will contain the value assigned to the @insidevar within your dynamic sql query

    Hope this helps

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

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