ASP and stored procedure problem (syntax error ?)

[Steven Scaife]

Hi i have created a stored procedure like the second attempt at creating
one, only this time i want to pass up to 5 values from my page to the stored
procedure to return a recordset that i can write back to the page.

i call the procedure with :

strSQL = "EXECUTE sp_sel_companynames @compname = '" & txtComp & "', @compno
= '" & cono & "', @regpcode = '" & regPcde & "', @tradpcode = '" & tradPcode
& "', @telno = '" & telNo & "'"

This is the 5 values i am passing into the procedure, i have based the
stored procedure on one in the following page

[url]http://www.sommarskog.se/dyn-search.html#dynintro[/url]

my stored procedure is as follows:

CREATE PROCEDURE sp_sel_companynames
@compname varchar(50) = null,
@compno varchar(5) = null,
@regpcode varchar(10) = null,
@tradpcode varchar(10) = null,
@telno varchar(12) = null
AS
DECLARE @sql nvarchar(4000)

SELECT @sql = 'SELECT CMPNY_NO, CMPNY_NAME, PREV_CMPNY_NAME, REG_POSTCODE,
STATUS, TRAD_POSTCODE, TELEPHONE FROM Company WHERE 1 = 1'

IF @compname IS NOT NULL
SELECT @sql = @sql + '( AND CMPNY_NAME like @compname%)'

IF @compno is not null
SELECT @sql=@sql + '(CMPNY_NO like @compno)'

IF @regpcode is not null
SELECT @sql=@sql + '(REG_POSTCODE like @regpcode)'

IF @tradpcode is not null
SELECT @sql=@sql + '(TRAD_POSTCODE like @tradpcode)'

IF @telno is not null
SELECT @sql=@sql + '(TELEPHONE like @telNo)'
GO

when i run the statement in query analyzer it says :

Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@sql'.

I have declared this in the procedure. If anyone can point me in the right
direction and help me realise where it is wrong i would be really grateful.

thanks in advance for any help

[Bob Barrows [MVP]]

Steven Scaife wrote:

> Hi i have created a stored procedure like the second attempt at
> creating one, only this time i want to pass up to 5 values from my
> page to the stored procedure to return a recordset that i can write
> back to the page.
>
> i call the procedure with :
>
> strSQL = "EXECUTE sp_sel_companynames @compname = '" & txtComp & "',
> @compno = '" & cono & "', @regpcode = '" & regPcde & "', @tradpcode =
> '" & tradPcode & "', @telno = '" & telNo & "'"
>
> This is the 5 values i am passing into the procedure, i have based the
> stored procedure on one in the following page
>
> [url]http://www.sommarskog.se/dyn-search.html#dynintro[/url]
>
> my stored procedure is as follows:
>
> CREATE PROCEDURE sp_sel_companynames
> @compname varchar(50) = null,
> @compno varchar(5) = null,
> @regpcode varchar(10) = null,
> @tradpcode varchar(10) = null,
> @telno varchar(12) = null
> AS
> DECLARE @sql nvarchar(4000)
>
> SELECT @sql = 'SELECT CMPNY_NO, CMPNY_NAME, PREV_CMPNY_NAME,
> REG_POSTCODE, STATUS, TRAD_POSTCODE, TELEPHONE FROM Company WHERE 1 =
> 1'
>
> IF @compname IS NOT NULL
> SELECT @sql = @sql + '( AND CMPNY_NAME like @compname%)'
>
> IF @compno is not null
> SELECT @sql=@sql + '(CMPNY_NO like @compno)'
>
> IF @regpcode is not null
> SELECT @sql=@sql + '(REG_POSTCODE like @regpcode)'
>
> IF @tradpcode is not null
> SELECT @sql=@sql + '(TRAD_POSTCODE like @tradpcode)'
>
> IF @telno is not null
> SELECT @sql=@sql + '(TELEPHONE like @telNo)'
> GO
>
> when i run the statement in query analyzer it says :
>
> Server: Msg 137, Level 15, State 2, Line 1
> Must declare the variable '@sql'.
>
> I have declared this in the procedure. If anyone can point me in the
> right direction and help me realise where it is wrong i would be
> really grateful.
>
> thanks in advance for any help

I'm not sure what statement you are running that is generating this error
message. Could you show us exactly what you put in QA to cause this error?

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Steven Scaife]

I am typing execute sp_sel_companynames @sql, I think this is why it is
falling over. Am i correct in assuming that when i call @sql i must specify
whats in it, i thought this was how you ran it. If i omit the @sql then it
executes but doesn't return a set, which is what i would expect, however if
i type
execute sp_sel_companynames @compname = 'McDonalds' it still doesn't return
any results into QA, i would expect around 15 names to appear.

Am i correct in assuming that i need to put the following into my procedure

exec sp_sel_companynames @sql, @compname, @compno, @regpcode, @tradpcode,
@telno before GO

I dont know why i think this, it just appears to be the norm in some
procedures. Sorry to be a pain. I am still learning.

thanks

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:O57VyWLgEHA.3536@TK2MSFTNGP12.phx.gbl...

> Steven Scaife wrote:

> > Hi i have created a stored procedure like the second attempt at
> > creating one, only this time i want to pass up to 5 values from my
> > page to the stored procedure to return a recordset that i can write
> > back to the page.
> >
> > i call the procedure with :
> >
> > strSQL = "EXECUTE sp_sel_companynames @compname = '" & txtComp & "',
> > @compno = '" & cono & "', @regpcode = '" & regPcde & "', @tradpcode =
> > '" & tradPcode & "', @telno = '" & telNo & "'"
> >
> > This is the 5 values i am passing into the procedure, i have based the
> > stored procedure on one in the following page
> >
> > [url]http://www.sommarskog.se/dyn-search.html#dynintro[/url]
> >
> > my stored procedure is as follows:
> >
> > CREATE PROCEDURE sp_sel_companynames
> > @compname varchar(50) = null,
> > @compno varchar(5) = null,
> > @regpcode varchar(10) = null,
> > @tradpcode varchar(10) = null,
> > @telno varchar(12) = null
> > AS
> > DECLARE @sql nvarchar(4000)
> >
> > SELECT @sql = 'SELECT CMPNY_NO, CMPNY_NAME, PREV_CMPNY_NAME,
> > REG_POSTCODE, STATUS, TRAD_POSTCODE, TELEPHONE FROM Company WHERE 1 =
> > 1'
> >
> > IF @compname IS NOT NULL
> > SELECT @sql = @sql + '( AND CMPNY_NAME like @compname%)'
> >
> > IF @compno is not null
> > SELECT @sql=@sql + '(CMPNY_NO like @compno)'
> >
> > IF @regpcode is not null
> > SELECT @sql=@sql + '(REG_POSTCODE like @regpcode)'
> >
> > IF @tradpcode is not null
> > SELECT @sql=@sql + '(TRAD_POSTCODE like @tradpcode)'
> >
> > IF @telno is not null
> > SELECT @sql=@sql + '(TELEPHONE like @telNo)'
> > GO
> >
> > when i run the statement in query analyzer it says :
> >
> > Server: Msg 137, Level 15, State 2, Line 1
> > Must declare the variable '@sql'.
> >
> > I have declared this in the procedure. If anyone can point me in the
> > right direction and help me realise where it is wrong i would be
> > really grateful.
> >
> > thanks in advance for any help

>
> I'm not sure what statement you are running that is generating this error
> message. Could you show us exactly what you put in QA to cause this error?
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

[Bob]

Steven Scaife wrote: 

Don't use the "sp_" prefix for user-defined, non-system stored procedures.
You are paying a small performance penalty for doing so, because SQL Server
assumes any procedure whose name begins with that prefix to be a system
procedure, causing it to look for the procedure in the Master database
before looking for it in your own database. Not only can this cause a
performance penalty, it can also cause the wrong procedure to run if you
give one of your procedures the same name as
 

Yes
 

You have no SELECT statement in your procedure that returns a resultset. All
of your SELECT statements assign a value to a variable. Here is my saved
reply on this subject:
There are 3 ways to return values from a SQL Server stored procedure:
1. a Select statement that returns a resultset
--run this script in Query Analyzer (QA):
Create Procedure SelectValue
(@input int)
AS
Select @input + 5
go
exec SelectValue 10
go
drop procedure SelectValue


2. a Return parameter:
--run this script in QA:
create procedure ReturnValue
(@input int)
AS
Return @input + 5
go
declare @returnvalue int
exec @returnvalue = ReturnValue 10
select @returnvalue
go
drop procedure ReturnValue

3. an Output Parameter:
--run this script in QA:
create procedure OutputValue
(@input int output)
AS
SET @input = @input + 5
go
declare @outputvalue int
SET @outputvalue = 10
exec OutputValue @outputvalue output
select @outputvalue
go
drop procedure OutputValue


I do not recommend method 1 for returning a single value. A resultset is
expensive to build, in that it must contain metadata in addition to data. So
more network traffic is created, and the client app needs to expend more
resources in order to retrieve and expose the resultset to the calling
procedure.

Most developers use the Return parameter to return status codes instead of
data. This is for the sake of consistency: there is no technical reason not
to use RETURN to return data, except that RETURN can only be used to return
integers. If you need to return other datatypes, you need to use an output
parameter.


Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Steven]

Well i think i am getting somewhere now, in my procedure i have added exec
@sql to the end of my procedure so it now runs the statement i have built.
However if i have my line like i originally did

SELECT @sql=@sql + ' AND (CMPNY_NAME like @compname%)' QA throws the
following error

Server: Msg 203, Level 16, State 2, Procedure sp_sel_companynames, Line 27
The name 'SELECT CMPNY_NO, CMPNY_NAME, PREV_CMPNY_NAME, REG_POSTCODE,
STATUS, TRAD_POSTCODE, TELEPHONE FROM Company WHERE 1 = 1 AND (CMPNY_NAME
like @compname %)' is not a valid identifier.

If i enter the line in my procedure as SELECT @sql=@sql + ' AND (CMPNY_NAME
like ' + @compname + '%)' it throws the following error

Server: Msg 203, Level 16, State 2, Procedure sp_sel_companynames, Line 27
The name 'SELECT CMPNY_NO, CMPNY_NAME, PREV_CMPNY_NAME, REG_POSTCODE,
STATUS, TRAD_POSTCODE, TELEPHONE FROM Company WHERE 1 = 1 AND (CMPNY_NAME
like tool%)' is not a valid identifier.

however this time it is showing my inputted value, if i then copy this into
QA and add ' before and after the company name the query runs.

So i am guessing that I somehow need to put the quote in, i have tried
SELECT @sql=@sql + ' AND (CMPNY_NAME like ''' + @compname + '''%)'
and also SELECT @sql=@sql + ' AND (CMPNY_NAME like ' + '' + @compname + '' +
'%)'

but still fails displaying

Server: Msg 203, Level 16, State 2, Procedure sp_sel_companynames, Line 27
The name 'SELECT CMPNY_NO, CMPNY_NAME, PREV_CMPNY_NAME, REG_POSTCODE,
STATUS, TRAD_POSTCODE, TELEPHONE FROM Company WHERE 1 = 1 AND (CMPNY_NAME
like 'tool%')' is not a valid identifier.

even though that appeasr to me to be correct

"Bob Barrows [MVP]" <SPAMcom> wrote in message
news:phx.gbl... 
>
> I'm not sure what statement you are running that is generating this error
> message. Could you show us exactly what you put in QA to cause this error?
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>[/ref]