How to use the variable in the SQL statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to use the variable in the SQL statement?

Author

Topic

danyeung
Posting Yak Master

102 Posts

Posted - 2006-04-05 : 11:34:20

I have the following SQL statement. I got syntax error if I use the varirable @Hierarchy.

declare @Hierarchy as varchar(20)
set @Hierarchy = '1 and 65'
SELECT vchrFieldDef as Labels
FROM tblRptFieldDef
where intRptHierarchyId between @Hierarchy

It works if I did the value instead of the variable.

declare @Hierarchy as varchar(20)
set @Hierarchy = '1 and 65'
SELECT vchrFieldDef as Labels
FROM tblRptFieldDef
where intRptHierarchyId between 1 and 65

How should the variable @Hierarchy be used in the query?

Thanks.
DanYeung

Kristen
Test

22859 Posts

Posted - 2006-04-05 : 11:40:42

[code]
declare @Hierarchy1 int,
@Hierarchy2 int
set @Hierarchy1 = 1,
@Hierarchy2 = 65
SELECT vchrFieldDef as Labels
FROM tblRptFieldDef
where intRptHierarchyId between @Hierarchy1 AND @Hierarchy2
[/code]
Kristen

danyeung
Posting Yak Master

102 Posts

Posted - 2006-04-05 : 12:47:54

Thanks. It is one sulotion. But I am looking for the generic solution how to use a variable in a where clause. Sometimes I want to build the where clause in the application and pass it in to the stored procedure, but it didn't work.

Thanks.
DanYeung

Kristen
Test

22859 Posts

Posted - 2006-04-05 : 12:55:28

Then you need to use Dynamic SQL:


declare @Hierarchy as varchar(20),
        @strSQL nvarchar(4000)
set @Hierarchy = '1 and 65'
SELECT @strSQL = 'SELECT vchrFieldDef as Labels
FROM tblRptFieldDef
where intRptHierarchyId between ' + @Hierarchy 
EXEC @strSQL

You could also look at using "parameterized" queries, e.g. using sp_ExecuteSQL, which will be cached by the query optimiser (which the above example will not be)

Kristen

danyeung
Posting Yak Master

102 Posts

Posted - 2006-04-05 : 13:27:35

I ran it in SQL Query Analyzer and received the follower error:

Could not find stored procedure 'SELECT vchrFieldDef as Labels FROM tblRptFieldDef where intRptHierarchyId between 1 and 65'.

What did I miss?

Thanks.
DanYeung

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-05 : 18:19:23

[code]declare @Hierarchy as varchar(20),
@strSQL nvarchar(4000)
set @Hierarchy = '1 and 65'
SELECT @strSQL = 'SELECT vchrFieldDef as Labels
FROM tblRptFieldDef
where intRptHierarchyId between ' + @Hierarchy
EXEC (@strSQL)[/code]

KH

Kristen
Test

22859 Posts

Posted - 2006-04-06 : 01:44:04

Oops! Sorry about that ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources