CODEWITHSUNDEEP
sqlsql-server-2008
Regeesh
Regeesh

Reputation: 25

space in a select statement in dynamic query

I have a dynamic query like this :

SET @str_Query = 'SELECT SIM.Item_ID,
                  SIM.Item_Description,
                  SU.Short_Description AS Unit,
                  SIM.Std_Lead_Time,'+
                  '' ''+' AS Last_Purchase_Rate
                  FROM  FKMS_Item_Master AS SIM
                        INNER JOIN FKMS_STP_Units SU
                        ON SIM.Item_Purchase_Unit=SU.Unit_Id' +
                 ' WHERE ' + @str_Condition + 
                            ' AND SIM.Location_Id =' + CAST(@aint_Location_Id AS VARCHAR(10)) +
                            ' AND SIM.Item_Deleted =0 
                              AND SIM.Approved_On IS NOT NULL'  
                +' ORDER BY SIM.Item_Description'

I want to retrieve space as Last_Purchase_Rate

It is showing syntax error in the portion of '' ''+' AS Last_Purchase_Rate when I execute this query. If I print this dynamic query, query seems correct. It shows as AS Last_Purchase_Rate with space before AS. Please help.

Upvotes: 2

Views: 487

Answers (4)

Aleksei Pugachev
Aleksei Pugachev

Reputation: 286

I would write

...SIM.Std_Lead_Time, '' '' AS Last_Purchase_Rate...

instead of

...SIM.Std_Lead_Time,'+'' ''+' AS Last_Purchase_Rate...

Upvotes: 2

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

You did not escape all quotes.

A working version of your statement would be

SET @str_Query = 'SELECT SIM.Item_ID,
                  SIM.Item_Description,
                  SU.Short_Description AS Unit,
                  SIM.Std_Lead_Time,'
                  + ''' '''
                  + ' AS Last_Purchase_Rate
                  FROM  FKMS_Item_Master AS SIM
                        INNER JOIN FKMS_STP_Units SU
                        ON SIM.Item_Purchase_Unit=SU.Unit_Id' +
                 ' WHERE ' + @str_Condition + 
                            ' AND SIM.Location_Id =' + CAST(@aint_Location_Id AS VARCHAR(10)) +
                            ' AND SIM.Item_Deleted =0 
                              AND SIM.Approved_On IS NOT NULL'  
                +' ORDER BY SIM.Item_Description'

but I find that with a little reformatting, the error is easier to spot

SET @str_Query = 
    'SELECT SIM.Item_ID '
    + ', SIM.Item_Description '
    + ', SU.Short_Description AS Unit '
    + ', SIM.Std_Lead_Time '
    + ', '' ''' + ' AS Last_Purchase_Rate '
    + 'FROM  FKMS_Item_Master AS SIM '
    + '      INNER JOIN FKMS_STP_Units SU '
    + '                   ON SIM.Item_Purchase_Unit=SU.Unit_Id ' 
    + ' WHERE ' + @str_Condition 
    + '       AND SIM.Location_Id = ' + CAST(@aint_Location_Id AS VARCHAR(10)) 
    + '       AND SIM.Item_Deleted =0 '
    + '       AND SIM.Approved_On IS NOT NULL '  
    + ' ORDER BY SIM.Item_Description '

Upvotes: 1

Piotr Auguscik
Piotr Auguscik

Reputation: 3681

Try using tsql function SPACE(1)

Upvotes: 0

Tim
Tim

Reputation: 28520

Why not use NULL instead of space and then handle the result in your app?

I.e.,

SET @str_Query = 'SELECT SIM.Item_ID,
              SIM.Item_Description,
              SU.Short_Description AS Unit,
              SIM.Std_Lead_Time,
              NULL AS Last_Purchase_Rate, -- and so on.

You could also use CHAR(32):

SET @str_Query = 'SELECT SIM.Item_ID,
              SIM.Item_Description,
              SU.Short_Description AS Unit,
              SIM.Std_Lead_Time,
              CHAR(32) AS Last_Purchase_Rate, -- and so on.

Upvotes: 1

Related Questions