Trouble passing variable in SELECT statement in Stored Procedure

Trouble passing variable in SELECT statement in Stored Procedure

Post by m2.. » Thu, 08 Feb 2001 23:04:18



I am passing a variable value to a stored procedure to SQL Server 7.0
via an ASP page on IIS 5.0. The code on the ASP page is as follows:
<%
Function DoSomething(strVar)
SQL = "sp_Foo '" & strVar & "'"
set rs = dBConnection.DBQuery(SQL)
     while not rs.EOF
       Response.write("<OPTION value=""" & trim(rs("item")) & """> " &
trim(rs("item")) & "</OPTION>")
     rs.MoveNext
     Wend
End Function
%>
<SELECT id=select3 name=variable1>
<OPTION selected value="">Any</OPTION>
<%
'***********************************************
'Pass the variablefield as the argument to the
'DoSomething funtion
'***********************************************
DoSomething("variable1")
%>
</SELECT>
<SELECT id=select3 name=variableN>
<OPTION selected value="">Any</OPTION>
<%
'***********************************************
'Pass the variablefield as the argument to the
'DoSomething funtion
'***********************************************
DoSomething("variableN")
%>
</SELECT>

The Stored Procedure looks like:
CREATE PROCEDURE sp_Foo

AS

NOT NULL

The record set only returns the strVar value in the drop down menu that
is created in the ASP page. When I place the variable directly in
stored procedure, it runs fine.

How can I code the stored procedure so that it will accept the
variable? What am I doing wrong? Any help would be appreciated.

Thanks

Sent via Deja.com
http://www.deja.com/

 
 
 

Trouble passing variable in SELECT statement in Stored Procedure

Post by DaveSat » Thu, 08 Feb 2001 23:18:30


something like:


AS

--
Thanks,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB 6.0/MTS
(Please reply to group only)
-----------------------------------------------------------------


> I am passing a variable value to a stored procedure to SQL Server 7.0
> via an ASP page on IIS 5.0. The code on the ASP page is as follows:
> <%
> Function DoSomething(strVar)
> SQL = "sp_Foo '" & strVar & "'"
> set rs = dBConnection.DBQuery(SQL)
>      while not rs.EOF
>        Response.write("<OPTION value=""" & trim(rs("item")) & """> " &
> trim(rs("item")) & "</OPTION>")
>      rs.MoveNext
>      Wend
> End Function
> %>
> <SELECT id=select3 name=variable1>
> <OPTION selected value="">Any</OPTION>
> <%
> '***********************************************
> 'Pass the variablefield as the argument to the
> 'DoSomething funtion
> '***********************************************
> DoSomething("variable1")
> %>
> </SELECT>
> <SELECT id=select3 name=variableN>
> <OPTION selected value="">Any</OPTION>
> <%
> '***********************************************
> 'Pass the variablefield as the argument to the
> 'DoSomething funtion
> '***********************************************
> DoSomething("variableN")
> %>
> </SELECT>

> The Stored Procedure looks like:
> CREATE PROCEDURE sp_Foo

> AS

> NOT NULL

> The record set only returns the strVar value in the drop down menu that
> is created in the ASP page. When I place the variable directly in
> stored procedure, it runs fine.

> How can I code the stored procedure so that it will accept the
> variable? What am I doing wrong? Any help would be appreciated.

> Thanks

> Sent via Deja.com
> http://www.deja.com/


 
 
 

Trouble passing variable in SELECT statement in Stored Procedure

Post by m2.. » Fri, 09 Feb 2001 00:10:24


David,
Thanks for the reply. I tried something similar to that earlier with:
CREATE PROCEDURE sp_Foo

AS




But it is not returning any record set content as I am getting the
following error in the ASP page:
Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: 'rs'

This is occuring the the line of code that looks for the rs.EOF as if
to say that there is no data being returned? Any further ideas anyone?

Mark



> something like:

> CREATE PROCEDURE sp_Foo

> AS


> --
> Thanks,
> David Satz
> Principal Software Engineer
> Hyperion Solutions
> ->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB
6.0/MTS
> (Please reply to group only)
> -----------------------------------------------------------------




- Show quoted text -

> > I am passing a variable value to a stored procedure to SQL Server
7.0
> > via an ASP page on IIS 5.0. The code on the ASP page is as follows:
> > <%
> > Function DoSomething(strVar)
> > SQL = "sp_Foo '" & strVar & "'"
> > set rs = dBConnection.DBQuery(SQL)
> >      while not rs.EOF
> >        Response.write("<OPTION value=""" & trim(rs("item"))
& """> " &
> > trim(rs("item")) & "</OPTION>")
> >      rs.MoveNext
> >      Wend
> > End Function
> > %>
> > <SELECT id=select3 name=variable1>
> > <OPTION selected value="">Any</OPTION>
> > <%
> > '***********************************************
> > 'Pass the variablefield as the argument to the
> > 'DoSomething funtion
> > '***********************************************
> > DoSomething("variable1")
> > %>
> > </SELECT>
> > <SELECT id=select3 name=variableN>
> > <OPTION selected value="">Any</OPTION>
> > <%
> > '***********************************************
> > 'Pass the variablefield as the argument to the
> > 'DoSomething funtion
> > '***********************************************
> > DoSomething("variableN")
> > %>
> > </SELECT>

> > The Stored Procedure looks like:
> > CREATE PROCEDURE sp_Foo

> > AS

> > NOT NULL

> > The record set only returns the strVar value in the drop down menu
that
> > is created in the ASP page. When I place the variable directly in
> > stored procedure, it runs fine.

> > How can I code the stored procedure so that it will accept the
> > variable? What am I doing wrong? Any help would be appreciated.

> > Thanks

> > Sent via Deja.com
> > http://www.deja.com/

Sent via Deja.com
http://www.deja.com/
 
 
 

Trouble passing variable in SELECT statement in Stored Procedure

Post by DaveSat » Fri, 09 Feb 2001 00:31:50


I'm not an guy, but does sp_Foo work in Query Analyzer ?

> David,
> Thanks for the reply. I tried something similar to that earlier with:
> CREATE PROCEDURE sp_Foo

> AS




> But it is not returning any record set content as I am getting the
> following error in the ASP page:
> Error Type:
> Microsoft VBScript runtime (0x800A01A8)
> Object required: 'rs'

> This is occuring the the line of code that looks for the rs.EOF as if
> to say that there is no data being returned? Any further ideas anyone?

> Mark



> > something like:

> > CREATE PROCEDURE sp_Foo

> > AS


> > --
> > Thanks,
> > David Satz
> > Principal Software Engineer
> > Hyperion Solutions
> > ->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB
> 6.0/MTS
> > (Please reply to group only)
> > -----------------------------------------------------------------



> > > I am passing a variable value to a stored procedure to SQL Server
> 7.0
> > > via an ASP page on IIS 5.0. The code on the ASP page is as follows:
> > > <%
> > > Function DoSomething(strVar)
> > > SQL = "sp_Foo '" & strVar & "'"
> > > set rs = dBConnection.DBQuery(SQL)
> > >      while not rs.EOF
> > >        Response.write("<OPTION value=""" & trim(rs("item"))
> & """> " &
> > > trim(rs("item")) & "</OPTION>")
> > >      rs.MoveNext
> > >      Wend
> > > End Function
> > > %>
> > > <SELECT id=select3 name=variable1>
> > > <OPTION selected value="">Any</OPTION>
> > > <%
> > > '***********************************************
> > > 'Pass the variablefield as the argument to the
> > > 'DoSomething funtion
> > > '***********************************************
> > > DoSomething("variable1")
> > > %>
> > > </SELECT>
> > > <SELECT id=select3 name=variableN>
> > > <OPTION selected value="">Any</OPTION>
> > > <%
> > > '***********************************************
> > > 'Pass the variablefield as the argument to the
> > > 'DoSomething funtion
> > > '***********************************************
> > > DoSomething("variableN")
> > > %>
> > > </SELECT>

> > > The Stored Procedure looks like:
> > > CREATE PROCEDURE sp_Foo

> > > AS

> > > NOT NULL

> > > The record set only returns the strVar value in the drop down menu
> that
> > > is created in the ASP page. When I place the variable directly in
> > > stored procedure, it runs fine.

> > > How can I code the stored procedure so that it will accept the
> > > variable? What am I doing wrong? Any help would be appreciated.

> > > Thanks

> > > Sent via Deja.com
> > > http://www.deja.com/

> Sent via Deja.com
> http://www.deja.com/

 
 
 

Trouble passing variable in SELECT statement in Stored Procedure

Post by m2.. » Fri, 09 Feb 2001 02:11:57


When running the stored proc in the query analyze, it returns an empty
record set (?)

Mark



> I'm not an guy, but does sp_Foo work in Query Analyzer ?




> > David,
> > Thanks for the reply. I tried something similar to that earlier
with:
> > CREATE PROCEDURE sp_Foo

> > AS




> > But it is not returning any record set content as I am getting the
> > following error in the ASP page:
> > Error Type:
> > Microsoft VBScript runtime (0x800A01A8)
> > Object required: 'rs'

> > This is occuring the the line of code that looks for the rs.EOF as
if
> > to say that there is no data being returned? Any further ideas
anyone?

> > Mark



> > > something like:

> > > CREATE PROCEDURE sp_Foo

> > > AS

WHERE " +

> > > --
> > > Thanks,
> > > David Satz
> > > Principal Software Engineer
> > > Hyperion Solutions
> > > ->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB
> > 6.0/MTS
> > > (Please reply to group only)
> > > -----------------------------------------------------------------



> > > > I am passing a variable value to a stored procedure to SQL
Server
> > 7.0
> > > > via an ASP page on IIS 5.0. The code on the ASP page is as
follows:
> > > > <%
> > > > Function DoSomething(strVar)
> > > > SQL = "sp_Foo '" & strVar & "'"
> > > > set rs = dBConnection.DBQuery(SQL)
> > > >      while not rs.EOF
> > > >        Response.write("<OPTION value=""" & trim(rs("item"))
> > & """> " &
> > > > trim(rs("item")) & "</OPTION>")
> > > >      rs.MoveNext
> > > >      Wend
> > > > End Function
> > > > %>
> > > > <SELECT id=select3 name=variable1>
> > > > <OPTION selected value="">Any</OPTION>
> > > > <%
> > > > '***********************************************
> > > > 'Pass the variablefield as the argument to the
> > > > 'DoSomething funtion
> > > > '***********************************************
> > > > DoSomething("variable1")
> > > > %>
> > > > </SELECT>
> > > > <SELECT id=select3 name=variableN>
> > > > <OPTION selected value="">Any</OPTION>
> > > > <%
> > > > '***********************************************
> > > > 'Pass the variablefield as the argument to the
> > > > 'DoSomething funtion
> > > > '***********************************************
> > > > DoSomething("variableN")
> > > > %>
> > > > </SELECT>

> > > > The Stored Procedure looks like:
> > > > CREATE PROCEDURE sp_Foo

> > > > AS

> > > > NOT NULL

> > > > The record set only returns the strVar value in the drop down
menu
> > that
> > > > is created in the ASP page. When I place the variable directly
in
> > > > stored procedure, it runs fine.

> > > > How can I code the stored procedure so that it will accept the
> > > > variable? What am I doing wrong? Any help would be appreciated.

> > > > Thanks

> > > > Sent via Deja.com
> > > > http://www.deja.com/

> > Sent via Deja.com
> > http://www.deja.com/

Sent via Deja.com
http://www.deja.com/
 
 
 

Trouble passing variable in SELECT statement in Stored Procedure

Post by DaveSat » Fri, 09 Feb 2001 02:55:32




> When running the stored proc in the query analyze, it returns an empty
> record set (?)

> Mark



> > I'm not an guy, but does sp_Foo work in Query Analyzer ?



> > > David,
> > > Thanks for the reply. I tried something similar to that earlier
> with:
> > > CREATE PROCEDURE sp_Foo

> > > AS




> > > But it is not returning any record set content as I am getting the
> > > following error in the ASP page:
> > > Error Type:
> > > Microsoft VBScript runtime (0x800A01A8)
> > > Object required: 'rs'

> > > This is occuring the the line of code that looks for the rs.EOF as
> if
> > > to say that there is no data being returned? Any further ideas
> anyone?

> > > Mark



> > > > something like:

> > > > CREATE PROCEDURE sp_Foo

> > > > AS

> WHERE " +

> > > > --
> > > > Thanks,
> > > > David Satz
> > > > Principal Software Engineer
> > > > Hyperion Solutions
> > > > ->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB
> > > 6.0/MTS
> > > > (Please reply to group only)
> > > > -----------------------------------------------------------------



> > > > > I am passing a variable value to a stored procedure to SQL
> Server
> > > 7.0
> > > > > via an ASP page on IIS 5.0. The code on the ASP page is as
> follows:
> > > > > <%
> > > > > Function DoSomething(strVar)
> > > > > SQL = "sp_Foo '" & strVar & "'"
> > > > > set rs = dBConnection.DBQuery(SQL)
> > > > >      while not rs.EOF
> > > > >        Response.write("<OPTION value=""" & trim(rs("item"))
> > > & """> " &
> > > > > trim(rs("item")) & "</OPTION>")
> > > > >      rs.MoveNext
> > > > >      Wend
> > > > > End Function
> > > > > %>
> > > > > <SELECT id=select3 name=variable1>
> > > > > <OPTION selected value="">Any</OPTION>
> > > > > <%
> > > > > '***********************************************
> > > > > 'Pass the variablefield as the argument to the
> > > > > 'DoSomething funtion
> > > > > '***********************************************
> > > > > DoSomething("variable1")
> > > > > %>
> > > > > </SELECT>
> > > > > <SELECT id=select3 name=variableN>
> > > > > <OPTION selected value="">Any</OPTION>
> > > > > <%
> > > > > '***********************************************
> > > > > 'Pass the variablefield as the argument to the
> > > > > 'DoSomething funtion
> > > > > '***********************************************
> > > > > DoSomething("variableN")
> > > > > %>
> > > > > </SELECT>

> > > > > The Stored Procedure looks like:
> > > > > CREATE PROCEDURE sp_Foo

> > > > > AS

> > > > > NOT NULL

> > > > > The record set only returns the strVar value in the drop down
> menu
> > > that
> > > > > is created in the ASP page. When I place the variable directly
> in
> > > > > stored procedure, it runs fine.

> > > > > How can I code the stored procedure so that it will accept the
> > > > > variable? What am I doing wrong? Any help would be appreciated.

> > > > > Thanks

> > > > > Sent via Deja.com
> > > > > http://www.deja.com/

> > > Sent via Deja.com
> > > http://www.deja.com/

> Sent via Deja.com
> http://www.deja.com/

 
 
 

Trouble passing variable in SELECT statement in Stored Procedure

Post by m2.. » Fri, 09 Feb 2001 03:23:49


David,



Thanks for the help



> When running the stored proc in the query analyze, it returns an empty
> record set (?)

> Mark



> > I'm not an guy, but does sp_Foo work in Query Analyzer ?



> > > David,
> > > Thanks for the reply. I tried something similar to that earlier
> with:
> > > CREATE PROCEDURE sp_Foo

> > > AS




> > > But it is not returning any record set content as I am getting the
> > > following error in the ASP page:
> > > Error Type:
> > > Microsoft VBScript runtime (0x800A01A8)
> > > Object required: 'rs'

> > > This is occuring the the line of code that looks for the rs.EOF as
> if
> > > to say that there is no data being returned? Any further ideas
> anyone?

> > > Mark



> > > > something like:

> > > > CREATE PROCEDURE sp_Foo

> > > > AS

> WHERE " +

> > > > --
> > > > Thanks,
> > > > David Satz
> > > > Principal Software Engineer
> > > > Hyperion Solutions
> > > > ->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB
> > > 6.0/MTS
> > > > (Please reply to group only)
> > > > ----------------------------------------------------------------
-



> > > > > I am passing a variable value to a stored procedure to SQL
> Server
> > > 7.0
> > > > > via an ASP page on IIS 5.0. The code on the ASP page is as
> follows:
> > > > > <%
> > > > > Function DoSomething(strVar)
> > > > > SQL = "sp_Foo '" & strVar & "'"
> > > > > set rs = dBConnection.DBQuery(SQL)
> > > > >      while not rs.EOF
> > > > >        Response.write("<OPTION value=""" & trim(rs("item"))
> > > & """> " &
> > > > > trim(rs("item")) & "</OPTION>")
> > > > >      rs.MoveNext
> > > > >      Wend
> > > > > End Function
> > > > > %>
> > > > > <SELECT id=select3 name=variable1>
> > > > > <OPTION selected value="">Any</OPTION>
> > > > > <%
> > > > > '***********************************************
> > > > > 'Pass the variablefield as the argument to the
> > > > > 'DoSomething funtion
> > > > > '***********************************************
> > > > > DoSomething("variable1")
> > > > > %>
> > > > > </SELECT>
> > > > > <SELECT id=select3 name=variableN>
> > > > > <OPTION selected value="">Any</OPTION>
> > > > > <%
> > > > > '***********************************************
> > > > > 'Pass the variablefield as the argument to the
> > > > > 'DoSomething funtion
> > > > > '***********************************************
> > > > > DoSomething("variableN")
> > > > > %>
> > > > > </SELECT>

> > > > > The Stored Procedure looks like:
> > > > > CREATE PROCEDURE sp_Foo

> > > > > AS

> > > > > NOT NULL

> > > > > The record set only returns the strVar value in the drop down
> menu
> > > that
> > > > > is created in the ASP page. When I place the variable directly
> in
> > > > > stored procedure, it runs fine.

> > > > > How can I code the stored procedure so that it will accept the
> > > > > variable? What am I doing wrong? Any help would be
appreciated.

> > > > > Thanks

> > > > > Sent via Deja.com
> > > > > http://www.deja.com/

> > > Sent via Deja.com
> > > http://www.deja.com/

> Sent via Deja.com
> http://www.deja.com/

Sent via Deja.com
http://www.deja.com/
 
 
 

Trouble passing variable in SELECT statement in Stored Procedure

Post by Erland Sommarsk » Mon, 12 Feb 2001 07:56:45



>CREATE PROCEDURE sp_Foo

>AS



Given the original query, this is completely meaningless. There
is absolutely no reason to write a stored procedure for this.
It just adds complexity with code in two places, and absolutely
no benefits at all.

It's a far better approach in this case to compose the SQL statement
in the client and send it to the server.

--