Dynamic SQL statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic SQL statement

Author

Topic

Angel
Starting Member

8 Posts

Posted - 2004-09-20 : 20:31:31

Hi,

I am trying write the following dynamic sql statement

declare @pagecount int
declare @lastid nvarchar(10)
declare @vsql nvarchar(1000)

select @vsql = 'select top ' + convert(nvarchar,pagecount) + ' @lastid = employeeid from employees'

execute (@vsql)

The error i am getting is "must declare @lastid"

I think I know the reason why it is giving me that error but is there any other way I can achieve what I want to do above?

Thanks,

Kal

jbkayne
Posting Yak Master

100 Posts

Posted - 2004-09-20 : 22:47:23

Kal,

You actually don't need dynamic sql to accomplish this.

declare @pagecount int
declare @lastid nvarchar(10)

set rowcount @pagecount

select @lastid = employeeid from employees

set rowcount 0

Angel
Starting Member

8 Posts

Posted - 2004-09-20 : 23:50:04

Thanks for the reply, but isn't using TOP more efficient then rowcount?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-21 : 02:40:31

first you may want to orient us on what do you intend to accomplish...

if you're still up to the dsql then...

select @vsql = 'select top ' + convert(nvarchar,pagecount) + ' ' + @lastid + '= employeeid from employees'

Angel
Starting Member

8 Posts

Posted - 2004-09-21 : 02:56:09

Hi Jen,

Here's the stored procedure I had written which I had like to modify

CREATE PROCEDURE [usp_GetEmployees]

@PREVIOUS NVARCHAR(10) = 'N/A' output,
@NEXT NVARCHAR(10) = 'N/A' output ,
@RECORDSPERPAGE INT = 10,
@RECORDCOUNT INT= 0 output,
@NOPREVIOUSRECORDS BIT = 0 OUTPUT

AS

DECLARE @LASTEMPLOYEEID NVARCHAR(100)

BEGIN
SET ROWCOUNT @RECORDSPERPAGE
-- PREVIOUS RECORDS
IF @PREVIOUS <> 'N/A'
BEGIN
SELECT * FROM EMPLOYEES WHERE EMPLOYEEID < @PREVIOUS ORDER BY EMPLOYEEID DESC
SELECT @LASTEMPLOYEEID = EMPLOYEEID FROM EMPLOYEE WHERE EMPLOYEEID < @PREVIOUS ORDER BY EMPLOYEEID DESC
END

-- NEXT RECORDS
IF @NEXT <> 'N/A'
BEGIN
SELECT * FROM EMPLOYEE WHERE EMPLOYEEID > @NEXT
SELECT @LASTEMPLOYEEID = EMPLOYEEID FROM EMPLOYEE WHERE EMPLOYEEID > @NEXT
END

-- FIRST TIME
IF @PREVIOUS = 'N/A' AND @NEXT = 'N/A'
BEGIN
SELECT * FROM EMPLOYEE
SELECT @LASTEMPLOYEEID = EMPLOYEEID FROM EMPLOYEE
END

--VERIFY IF THERE ARE ANY MORE PREVIUOS RECORDS
SELECT @RECORDCOUNT = COUNT(*) FROM EMPLOYEE WHERE EMPLOYEEID < @LASTEMPLOYEEID

IF (@RECORDCOUNT - @RECORDSPERPAGE) <= 0
BEGIN
SET @NOPREVIOUSRECORDS = 1
END

--GET RECORD COUNT
SELECT @RECORDCOUNT = COUNT(*) FROM EMPLOYEE WHERE EMPLOYEEID > @LASTEMPLOYEEID

SET ROWCOUNT 0
END

GO

Angel
Starting Member

8 Posts

Posted - 2004-09-21 : 02:59:01

Also Jen,

I have earlier tried the way you sugested

select @vsql = 'select top ' + convert(nvarchar,pagecount) + ' ' + @lastid + '= employeeid from employees'

For obvious reasons the sql statement will look like

"select top 10 = employeeid from employees"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-21 : 03:09:08

quote:
Originally posted by Angel

For obvious reasons the sql statement will look like

"select top 10 = employeeid from employees"

yap, if you remove the "=" sign will that do?

Angel
Starting Member

8 Posts

Posted - 2004-09-21 : 03:16:14

well that won't surve the purpose would it?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-21 : 03:19:54

i'm not in the reading mode right now, but what do you intend to do?

i was only troubleshooting the snippet you gave...

quote:

Hi,

I am trying write the following dynamic sql statement

declare @pagecount int
declare @lastid nvarchar(10)
declare @vsql nvarchar(1000)

select @vsql = 'select top ' + convert(nvarchar,pagecount) + ' @lastid = employeeid from employees'

execute (@vsql)

The error i am getting is "must declare @lastid"

I think I know the reason why it is giving me that error but is there any other way I can achieve what I want to do above?

Thanks,

Kal

Angel
Starting Member

8 Posts

Posted - 2004-09-21 : 03:32:19

Hi Jen,

If you look at my stored procedure I had posted earlier, I am using ROWCOUNT and I had like use TOP operator instead.

Thanks,

Kal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources