February 13, 2006 at 12:44 pm
I'm getting an error
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '@RETURNEDROWS'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '0'.
When I try to return the results of a select statement (below) into a variable....
1 DECLARE @RETURNEDROWS INT
2 SELECT STATUS, SCHOOLNUM, STULINK, [SEQUENCE], USERFIELD1, USERFIELD2,
3 USERSTAMP, DATESTAMP, [TIMESTAMP],
4 SCHLYEAR,FROMDATE,TODATE,SCHLATTNUM,SCHLATTNM,
5 CITY,STATE,GRADELEVEL, COUNTRY, BYAUTOPROC, EOYSTATUS, RESERVED,FILLER,
6 SCHOOLFLAG, DISTNO, FLSCHLNO, U$DATE, PROVINCE
7 FROM @SCHOOLNUM2 AS @RETURNEDROWS
Any idea what I'm missing here?
February 13, 2006 at 12:47 pm
You can't put a result set into an INT. Are you looking for the count?...
DECLARE @RETURNEDROWS INT
SELECT STATUS, SCHOOLNUM, STULINK, [SEQUENCE], USERFIELD1, USERFIELD2,
USERSTAMP, DATESTAMP, [TIMESTAMP],
SCHLYEAR,FROMDATE,TODATE,SCHLATTNUM,SCHLATTNM,
CITY,STATE,GRADELEVEL, COUNTRY, BYAUTOPROC, EOYSTATUS, RESERVED,FILLER,
SCHOOLFLAG, DISTNO, FLSCHLNO, U$DATE, PROVINCE
FROM @SCHOOLNUM2
SET @RETURNEDROWS = @@ROWCOUNT
February 13, 2006 at 12:47 pm
You can't assign a multi-column result set into a single variable. General syntax is:
SELECT
@Var1 = Column1,
@Var2 = Column2,
...
@VarN = ColumnN
FROM
WHERE etc
February 13, 2006 at 11:09 pm
The Table alise name can't be a variable name.
February 14, 2006 at 5:11 am
If you want to put the result of a select (multiple rows) into a variable, you have to declare that variable as TABLE variable. Otherwise, the variable can have only one value. When using a table variable, you can use the same syntax as if it were a normal table.
DECLARE @result TABLE (col_a int, col_b char(1))
INSERT INTO @result (col_a, col_b) values (1, 'x')
INSERT INTO @result (col_a, col_b) values (2, 'y')
SELECT * FROM @result
That means, you have to name all columns and their data types in the DECLARE, and then again when inserting.
DECLARE @returnedrows TABLE (..here definition of columns w. datatypes..)
INSERT INTO @returnedrows (STATUS, SCHOOLNUM, STULINK, [SEQUENCE], USERFIELD1, USERFIELD2, USERSTAMP, DATESTAMP, [TIMESTAMP], SCHLYEAR, FROMDATE, TODATE, SCHLATTNUM,SCHLATTNM, CITY, STATE, GRADELEVEL, COUNTRY, BYAUTOPROC, EOYSTATUS, RESERVED, FILLER, SCHOOLFLAG, DISTNO, FLSCHLNO, U$DATE, PROVINCE
SELECT STATUS, SCHOOLNUM, STULINK, [SEQUENCE], USERFIELD1, USERFIELD2, USERSTAMP, DATESTAMP, [TIMESTAMP], SCHLYEAR, FROMDATE,TODATE, SCHLATTNUM, SCHLATTNM, CITY, STATE, GRADELEVEL, COUNTRY, BYAUTOPROC, EOYSTATUS, RESERVED, FILLER, SCHOOLFLAG, DISTNO, FLSCHLNO, U$DATE, PROVINCE
FROM ... some table/s
WHERE ... some condition/s
If the variable @returnedrows should really hold only the number of rows, you can use either @@rowcount as Amit suggested, or set the value from a SELECT COUNT(*) statement.
March 26, 2009 at 3:37 pm
Is there any way to get a @variable to work as a table name?
i.e.
Select * from @tablename
:unsure:
March 26, 2009 at 5:43 pm
newbie_new (3/26/2009)
Is there any way to get a @variable to work as a table name?i.e.
Select * from @tablename
Yes, you can do this with dynamic SQL, but unless you are careful you could lay yourself open to SQL injection attacks. Here's the potentially dangerous way to do it.
DECLARE @tablename nvarchar(128)
DECLARE @sqlUnsafe nvarchar(1000)
SELECT @tablename = N'dbo.MyTable;DELETE FROM UserAccounts'
SELECT @sqlUnsafe = N'SELECT * FROM ' + @tablename
PRINT @sqlUnsafe
--EXEC(@sqlUnsafe)
Here's a safer way to do it that validates the table name against the INFORMATION_SCHEMA.TABLES view.
DECLARE @tablename nvarchar(128)
DECLARE @sqlSafe nvarchar(1000)
SELECT @tablename = N'dbo.MyTable'
SELECT @sqlSafe = N'SELECT * FROM [' + TABLE_SCHEMA + N'].[' + TABLE_NAME + N']'
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_NAME = PARSENAME(@tablename, 1))
AND (TABLE_SCHEMA = COALESCE(PARSENAME(@tablename, 2), N'dbo'))
PRINT @sqlSafe
IF (@sqlSafe IS NULL)
PRINT 'Specified table name is not valid'
ELSE
EXEC(@sqlSafe)
March 27, 2009 at 9:03 am
oh quite nasty!!!
Thanks!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply