CODEWITHSUNDEEP
sqlsql-server
rjbogz
rjbogz

Reputation: 870

Select into variable as string

Can anyone tell me why this doesn't work?

DECLARE
  @ItemList         VARCHAR(max);

IF EXISTS
    (SELECT IM_Data INTO ItemList
      FROM IM_DataTable
      WHERE xRowNum<=1)

Error:

Lookup Error - SQL Server Database Error: Incorrect syntax near the keyword 'INTO'.

Upvotes: 0

Views: 5403

Answers (4)

chris
chris

Reputation: 638

You have left off the @ sign that needs to be at the beginning of the variable name. Also, it seems like you are mixing together the syntax to check for existence and the syntax to retrieve data into a variable. Are you trying to just check for existence of a certain row or rows in IN_DataTable? If so, leave out the "INTO @ItemList" so that you just have the following:

IF EXISTS 
    (SELECT IM_Data
     FROM IN_DataTable
     WHERE xRowNum <= 1)
...

If you need to retrieve the IM_Data and use it later, you should do something like this:

DECLARE @ItemList VARCHAR(max);
SELECT @ItemList = IM_Data 
FROM IM_DataTable
WHERE xRowNum<=1;
...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Why doesn't this work?

Well, the subquery that you have is:

SELECT IM_Data
INTO ItemList
FROM IM_DataTable
WHERE xRowNum<=1

This is perfectly valid syntax for creating a table, called ItemList, in the current database. However, this syntax is not allowed in an if statement.

So you could phrase this as:

SELECT IM_Data
INTO ItemList
FROM IM_DataTable
WHERE xRowNum<=1

if exists (select * from itemlist)

Or you could dispense with the additional table altogether:

if exists (select * from IM_DataTable WHERE xRowNum<=1)

Or, you might have some other intention altogether.

Upvotes: 0

Lamak
Lamak

Reputation: 70638

Ok, first of all, you need to make sure that the result from your query is only one row, so I don't see the need for the WHERE xRowNum<=1, you should use xRowNum=1:

DECLARE @ItemList VARCHAR(max);

SELECT @ItemList = IM_Data
FROM IM_DataTable
WHERE xRowNum = 1

Upvotes: 5

Phil Sandler
Phil Sandler

Reputation: 28016

I would think it's expecting ItemList to be a table--you SELECT INTO a table, not a variable.

Syntax in SQL Server is:

SELECT 
   ItemList = IM_Data
FROM 
   IM_DataTable
WHERE
   xRowNum<=1

I'm not sure how the IF EXISTS fits into that. You want to set the variable only if data exists?

Upvotes: 1

Related Questions