Creating dynamic sql where operator is dynamic? - 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)

Creating dynamic sql where operator is dynamic?

Author

Topic

Baresi
Starting Member

4 Posts

Posted - 2004-08-11 : 21:07:02

Hi,

This is one of the 'nice-to-hv' options that my users want.
Basically, what I'm trying to do is create a stored procedure that takes in a few parameters, one of which is an operator, be it a >, >=, <, = etc. This will be selected via the interface.

However, I tried placing it right into the SP< tried string building and Exec-ing a SQL string but none work. The latter returned an error being unable to convert varchar to int.

ANybody done this type of thing before? Any tips or code samples would be of great help.

Thanks.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-11 : 21:15:43

This is possible using dynamic sql.

Can you post the SP contents for us to have a look at?

Baresi
Starting Member

4 Posts

Posted - 2004-08-11 : 22:09:27

Well, this is what I have right now. I converted this over from a regular SP I wrote, so hopefully its not a syntax error I made thats causing the problem.


CREATE PROCEDURE CertRep
(
    @Dept Int,
    @Sect Int,
    @Certs Int,
    @Sign VarChar(5)
)
As

Declare @SQL Varchar(3000)

Set @SQL = 'Select Staff.StaffID As StaffID, Staff.Name As Name, DeptSec.DeptID As Dept, Staff.Section As Section, Staff.NumCert As NumCert
From Staff, Section, DeptSec
Where Staff.Section=Section.ID And
Section.ID=DeptSec.SectID And
Staff.Section= '
    If @Sect=-1 Set @SQL = @SQL + 'Staff.Section'
    If @Sect <>-1 Set @SQL = @SQL + @Sect
Set @SQL = @SQL + ' And DeptSec.DeptID= '
    If @Sect=-1
            If @Dept=-1 Set @SQL = @SQL + 'DeptSec.DeptID'
            If @Dept<>-1 Set @SQL = @SQL + @Dept
    If @Sect<>-1 Set @SQL = @SQL + 'DeptSec.DeptID'
Set @SQL = @SQL + ' And NumCert '
Set @SQL = @SQL + @Sign + @Certs
Set @SQL = @SQL + ' Order By NumCert DESC, StaffID'
GO

My error right now is
Syntax error converting the varchar value 'Select Staff.StaffID As StaffID, Staff.Name As Name, DeptSec.DeptID As Dept, Staff.Section As Section, Staff.NumCert As NumCert From Staff, Section, DeptSec Where Staff.Section=Section.ID And Section.ID=DeptSec.SectID And Staff.Section= Staff.Section And DeptSec.DeptID= DeptSec.DeptID And NumCert >' to a column of data type int.

Thanks again :)

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-12 : 00:02:39

You need to convert your integer values to nvarchar's before you can concatenate:
e.g.

If @Sect <>-1 Set @SQL = @SQL + @Sect

should become

If @Sect <>-1 Set @SQL = @SQL + Convert(nvarchar, @Sect)

It's nothing to do with your operator.

Baresi
Starting Member

4 Posts

Posted - 2004-08-12 : 02:34:51

Thanks, its working as expected now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources