T-SQL Variables

10 Nov 2005 5:05 PM

orourksj

I am trying to select for an insert. I have pasted the attempted code below
that I have used for testing purposes. Query Analyzer will not accept the
'top' and a variable. I have tries Casting and converting even though i have
declared the variable an integer.

Declare @RowCount int
Set @RowCount = 3--(Select Count(*)From #u1)
declare @strsql varchar(1000)
set @strsql = 'select Top ' + Cast(@RowCount as int) + '
* from coaccess c1 with (Nolock)
where coa_userisco = 1
and coa_zonecoid = 199
order by coA_ZoneID'

Print @strsql

WHen I run this I get the error:
Server: Msg 245, Level 16, State 1, Line 5
Syntax error converting the varchar value 'select Top ' to a column of data
type int.

Can someone help?

10 Nov 2005 5:38 PM

Trey Walpole

it needs to be converted to character (varchar or char) in this context,
to concatenate

with a variable, you can set rowcount first, then run the query, then
set it back, e.g.

set rowcount @rowcount

select ...etc...

set rowcount 0

orourksj wrote:

Show quote

>I am trying to select for an insert. I have pasted the attempted code below
>that I have used for testing purposes. Query Analyzer will not accept the
>'top' and a variable. I have tries Casting and converting even though i have
>declared the variable an integer.
>
>Declare @RowCount int
>Set @RowCount = 3--(Select Count(*)From #u1)
>declare @strsql varchar(1000)
>set @strsql = 'select Top ' + Cast(@RowCount as int) + '
> * from coaccess c1 with (Nolock)
>where coa_userisco = 1
>and coa_zonecoid = 199
>order by coA_ZoneID'
>
>Print @strsql
>
>WHen I run this I get the error:
>Server: Msg 245, Level 16, State 1, Line 5
>Syntax error converting the varchar value 'select Top ' to a column of data
>type int.
>
>Can someone help?
>
>

10 Nov 2005 5:38 PM

Tibor Karaszi

Change:
Cast(@RowCount as int)

to:
Cast(@RowCount as varchar(10))

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"orourksj" <orour***@discussions.microsoft.com> wrote in message
news:B166AABA-3548-4A9B-BED6-9D5A06975C1F@microsoft.com...
>I am trying to select for an insert. I have pasted the attempted code below
> that I have used for testing purposes. Query Analyzer will not accept the
> 'top' and a variable. I have tries Casting and converting even though i have
> declared the variable an integer.
>
> Declare @RowCount int
> Set @RowCount = 3--(Select Count(*)From #u1)
> declare @strsql varchar(1000)
> set @strsql = 'select Top ' + Cast(@RowCount as int) + '
> * from coaccess c1 with (Nolock)
> where coa_userisco = 1
> and coa_zonecoid = 199
> order by coA_ZoneID'
>
> Print @strsql
>
> WHen I run this I get the error:
> Server: Msg 245, Level 16, State 1, Line 5
> Syntax error converting the varchar value 'select Top ' to a column of data
> type int.
>
> Can someone help?

10 Nov 2005 5:47 PM

John Scragg

You are casting to the wrong datatype. You should be casting to varchar.

set @strsql = 'select Top ' + Cast(@RowCount as int) + '

should read

set @strsql = 'select Top ' + Cast(@RowCount as varchar) + '

After all, it is already a int, no need to cast it to itself :P But to
concatenate it like this, it needs to be cast to a varchar (or char).

HTH,

John Scragg

Show quote

"orourksj" wrote:

> I am trying to select for an insert. I have pasted the attempted code below
> that I have used for testing purposes. Query Analyzer will not accept the
> 'top' and a variable. I have tries Casting and converting even though i have
> declared the variable an integer.
>
> Declare @RowCount int
> Set @RowCount = 3--(Select Count(*)From #u1)
> declare @strsql varchar(1000)
> set @strsql = 'select Top ' + Cast(@RowCount as int) + '
> * from coaccess c1 with (Nolock)
> where coa_userisco = 1
> and coa_zonecoid = 199
> order by coA_ZoneID'
>
> Print @strsql
>
> WHen I run this I get the error:
> Server: Msg 245, Level 16, State 1, Line 5
> Syntax error converting the varchar value 'select Top ' to a column of data
> type int.
>
> Can someone help?