Professional Web Applications Themes

ASP and stored procedure problem (syntax error ?) - ASP Database

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, ...

  1. #1

    Default ASP and stored procedure problem (syntax error ?)

    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 yzer 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


    Steven Scaife Guest

  2. #2

    Default Re: ASP and stored procedure problem (syntax error ?)

    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 yzer 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 Barrows [MVP] Guest

  3. #3

    Default Re: ASP and stored procedure problem (syntax error ?)

    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]" <reb01501NOyahoo.SPAMcom> wrote in message
    news:O57VyWLgEHA.3536TK2MSFTNGP12.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 yzer 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 Guest

  4. #4

    Default Re: ASP and stored procedure problem (syntax error ?)

    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 yzer (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"


    Bob Guest

  5. #5

    Default Re: ASP and stored procedure problem (syntax error ?)

    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]


    Steven Guest

Similar Threads

  1. Simple Stored Procedure Error
    By ism in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 29th, 11:16 PM
  2. Stored procedure error
    By HariMaki in forum Macromedia Dynamic HTML
    Replies: 1
    Last Post: December 3rd, 12:59 PM
  3. description of error in stored procedure
    By Ola Fjelddahl in forum IBM DB2
    Replies: 2
    Last Post: September 4th, 03:46 PM
  4. C stored procedure SQL error.
    By Craig in forum IBM DB2
    Replies: 2
    Last Post: July 11th, 04:44 PM
  5. Stored Procedure Servers Error
    By Adam Campbell in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 8th, 02:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139