Professional Web Applications Themes

Stored Procedure Syntax - Microsoft SQL / MS SQL Server

I have been struggling over this for a long time but now I need help. The syntax of the last line of the following stored procedure is wrong. I would be extremely grateful to anyone who can help me with this. Many thanks, Meerkat. USE Northwind IF OBJECT_ID('dbo.SP_WWV_TEST') IS NOT NULL DROP PROC dbo.SP_WWV_TEST GO CREATE PROCEDURE SP_WWV_TEST OUT_TOTAL_RECORDS int OUTPUT, IN_TOWN VARCHAR(50) AS DECLARE AT_TOWN VARCHAR(50) DECLARE AT_SQL VARCHAR(1024) SELECT AT_TOWN = '''' + IN_TOWN + '''' SELECT AT_SQL = 'SELECT CustomerID,CompanyName,City FROM Customers WHERE City = ' + AT_TOWN EXECUTE(AT_SQL) -- The Syntax of the following line is ...

  1. #1

    Default Stored Procedure Syntax

    I have been struggling over this for a long time but now I need help.
    The syntax of the last line of the following stored procedure is
    wrong.
    I would be extremely grateful to anyone who can help me with this.

    Many thanks, Meerkat.

    USE Northwind

    IF OBJECT_ID('dbo.SP_WWV_TEST') IS NOT NULL
    DROP PROC dbo.SP_WWV_TEST
    GO

    CREATE PROCEDURE SP_WWV_TEST
    OUT_TOTAL_RECORDS int OUTPUT,
    IN_TOWN VARCHAR(50)
    AS

    DECLARE AT_TOWN VARCHAR(50)
    DECLARE AT_SQL VARCHAR(1024)

    SELECT AT_TOWN = '''' + IN_TOWN + ''''

    SELECT AT_SQL = 'SELECT CustomerID,CompanyName,City FROM Customers
    WHERE City = ' + AT_TOWN

    EXECUTE(AT_SQL)

    -- The Syntax of the following line is wrong but I am trying to set
    -- OUT_TOTAL_RECORDS to the number of records where City = AT_TOWN
    -- I really hope I have explained myself properly.
    SELECT OUT_TOTAL_RECORDS = 'SELECT Count(CustomerID) from Customers
    WHERE City = ' + AT_TOWN



    Meerkat Guest

  2. #2

    Default Re: Stored Procedure Syntax

    Meerkat () writes: 

    I don't really see why you use dynamic SQL at all?
     

    Don't call your procedures sp_something. The sp_ prefix is reserved
    for system procedures, and SQL Server first looks in master for these.

    To see how get back a value from a dynamic SQL statement, see
    http://www.algonet.se/~sommar/dynamic_sql.html#sp_executesql.


    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  3. #3

    Default Re: Stored Procedure Syntax

    i dont know if this is your actual script or just a sample.
    if this is your actual script, why use dynamic sql instead of
    using the standard approach, i think the required output is
    pretty straightforward so you do not need to used dynamic sql.


    --
    gani




    <Meerkat> wrote in message
    news:com... 


    gani Guest

  4. #4

    Default Re: Stored Procedure Syntax

    Thanks for the reply Erland.
    I note your advice about the procedure name.
    I call the stored procedure from a web page.
    I read the article:
    "The curse and Blessings of Dynamic SQL"
    but could not find the answer to by question.
    No chance of just telling me I suppose.

    No? oh well, thanks anyway - it was worth a try!

    Meerkat


    On Mon, 25 Aug 2003 07:34:29 +0000 (UTC), Erland Sommarskog
    <se> wrote:
     
    >
    >I don't really see why you use dynamic SQL at all?

    >
    >Don't call your procedures sp_something. The sp_ prefix is reserved
    >for system procedures, and SQL Server first looks in master for these.
    >
    >To see how get back a value from a dynamic SQL statement, see
    >http://www.algonet.se/~sommar/dynamic_sql.html#sp_executesql.[/ref]

    meerkat-nospam@wanadoo.fr Guest

  5. #5

    Default Re: Stored Procedure Syntax

    (fr) writes: 

    The specific section I referred you to is the one that describes
    sp_executesql, and how you can use it to get values back from a
    dynamic SQL statement. Check the example in this section:

    http://www.algonet.se/~sommar/dynamic_sql.html#sp_executesql.




    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  6. #6

    Default Re: Stored Procedure Syntax

    Got It !
    Thanks very much for your help and patience Erland.
    Meerkat

    On Mon, 25 Aug 2003 20:31:21 +0000 (UTC), Erland Sommarskog
    <se> wrote:
     
    >
    >The specific section I referred you to is the one that describes
    >sp_executesql, and how you can use it to get values back from a
    >dynamic SQL statement. Check the example in this section:
    >
    >http://www.algonet.se/~sommar/dynamic_sql.html#sp_executesql.
    >[/ref]

    Meerkat Guest

Similar Threads

  1. Stored Procedure Syntax error.
    By Ryan in forum MySQL
    Replies: 2
    Last Post: December 21st, 09:19 PM
  2. ASP and stored procedure problem (syntax error ?)
    By Steven Scaife in forum ASP Database
    Replies: 4
    Last Post: August 13th, 12:11 PM
  3. Error 170 (T-SQL syntax) in procedure
    By Tom in forum Microsoft SQL / MS SQL Server
    Replies: 12
    Last Post: August 14th, 03:49 PM
  4. stored procedure syntax help
    By anjelina in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: July 14th, 10:58 AM

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