Issue with Dynamic SP

Question

Post reply

Issue with Dynamic SP

hitek2000

SSC Veteran

Points: 214
More actions
February 26, 2004 at 6:14 pm

#107670

Hello,
I would like to thank Frank Kalis for referring me to the dynamic SQL link below... it was a big help
http://www.sommarskog.se/dynamic_sql.html
However, I do have one issue...... My Query works fine in the Query Analyzer, but when trying to pass form variables to the stored procedure in ASP - Set objRS = Conn.Execute("search_orders_1 '" & request.form("Group_Name") & "'") , I keep getting the following error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to int.
Here is the SP:
CREATE PROCEDURE search_orders_1
                    @id     int          = NULL,
                    @Region     varchar(20) = NULL,
                    @SMC          varchar(50) = NULL,
                    @ATS          varchar(10) = NULL,
                    @Platform     varchar(50) = NULL,
                    @Server_Name varchar(50) = NULL,
                    @Owner      varchar(50) = NULL,
                    @Group_Name varchar(255) = NULL,
                    @Scope      varchar(50) = NULL,
                    @Cost         varchar(100) = NULL,
                    @Priority     varchar(50) = NULL,
                    @debug        bit          = 0 AS


   DECLARE @sql        nvarchar(4000),
           @paramlist nvarchar(4000)

   SELECT @sql =
       'SELECT d.id, d.Region, d.SMC, d.ATS,
               d.Platform, d.Server_Name, d.Owner, d.Group_Name, d.Scope,
               d.Cost, d.Priority
        FROM   deepdive d
        WHERE 1 = 1'


   IF @id IS NOT NULL
      SELECT @sql = @sql + ' AND d.id = @xid'

   IF @Region IS NOT NULL
      SELECT @sql = @sql + ' AND d.Region = @xRegion'

   IF @SMC IS NOT NULL
      SELECT @sql = @sql + ' AND d.SMC = @xSMC'

   IF @ATS IS NOT NULL
      SELECT @sql = @sql + ' AND d.ATS LIKE @xATS + ''%'''

   IF @Platform IS NOT NULL
      SELECT @sql = @sql + ' AND d.Platform = @xPlatform'

   IF @Server_Name IS NOT NULL
      SELECT @sql = @sql + ' AND d.Server_Name LIKE @xServer_Name + ''%'''

   IF @Owner IS NOT NULL
      SELECT @sql = @sql + ' AND d.Owner LIKE @xOwner + ''%'''

   IF @Group_Name IS NOT NULL
      SELECT @sql = @sql + ' AND d.Group_Name = @xGroup_Name'

   IF @Scope IS NOT NULL
      SELECT @sql = @sql + ' AND d.Scope = @xScope'

   IF @Cost IS NOT NULL
      SELECT @sql = @sql + ' AND d.Cost = @xCost'

   IF @Priority IS NOT NULL
      SELECT @sql = @sql + ' AND d.Priority = @xPriority'


   SELECT @sql = @sql + ' ORDER BY d.id'

   IF @debug = 1
      PRINT @sql

   SELECT @paramlist = '@xid     int,
                        @xRegion varchar(20) ,
                        @xSMC    varchar(50) ,
                        @xATS   varchar(10) ,
                        @xPlatform varchar(50) ,
                        @xServer_Name   varchar(50) ,
                        @xOwner varchar(50) ,
                        @xGroup_Name   varchar(255) ,
                        @xScope   varchar(50) ,
                        @xCost   varchar(100) ,
                        @xPriority  varchar(50) '



   EXEC sp_executesql @sql, @paramlist,
                      @id, @Region, @SMC, @ATS, @Platform,
                      @Server_Name, @Owner, @Group_Name, @Scope, @Cost,
                      @Priority
GO

Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

You have to name your parameter or it will assume it the first parameter is being entered.

Conn.Execute("search_orders_1 @xGroup_Name = '" & request.form("Group_Name") & "'")

noeld SSC Guru Points: 96590 More actions · Answer 2

besides the above correction I believe that to use the connection object to execute the procedure is not the best way I would recommend you use the command object instead

* Noel

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

No problem!

Although it does seem so, I'm not totally against the use of dynamic sql. There are cases with very complex searches where you are better off with dynamic sql, but these are few.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 4

I am wondering if the SP works at all with the syntax that it is written with currently , I believe the variables should not be included inside the apostophies like it should be

IF @id IS NOT NULL

SELECT @sql = @sql + ' AND d.id = @xid'

should be

IF @id IS NOT NULL

SELECT @sql=@sql+ ' AND d.id ='+ ''+ @xid + ''

Otherwise the variables may not get substituted with the passed in values as even the variable will be considered as part of the string, and if the variable is of int or numeric type then it can be written as

SELECT @sql=@sql+ ' AND d.id ='+ @xid

I don't know if I am missing anything here.

Thanks

Prasad Bhogadi
www.inforaise.com