My First Stored Procedure

Results 1 to 2 of 2

Thread: My First Stored Procedure

  1. #1
    Join Date
    Dec 1969

    Default My First Stored Procedure

    I am working today on my first stored procedure and am asking for help to get unstuck. What do I have to do to get the stored procedure to act the same as the ADO/T-SQL connection? I have the following normal code to access a table. I also have a copy of what I am trying in a stored procedure to do the same exact thing, only it has to be dynamic in grabbing the different PR_SKU for different products.<BR><BR>&#060;%<BR>Dim conn<BR>Dim rs, sSql<BR>Set conn = Server.CreateObject("ADODB.Connection")<BR>conn.Op en "DSN=DB;UID=myid;PWD=pass"<BR>Set rs = Server.CreateObject("ADODB.Recordset")<BR>sSql = "SELECT * FROM PRODUCTS WHERE PR_SKU = &#039MUEQKDM1&#039"<BR>rs.Open sSql, conn, 1, 3, 0<BR>Response.Write "$" & rs("PR_UnitPrice")<BR>rs.Close<BR>conn.Close<BR>Se t rs = Nothing<BR>Set conn = Nothing<BR>%&#062;<BR><BR> Alter Procedure StoredProcedure1<BR> (<BR> @PR_UnitPrice varchar(11) = default value<BR> )<BR>As<BR>SELECT PR_UnitPrice FROM PRODUCTS WHERE (PR_SKU = @PR_SKU)<BR> return

  2. #2 Guest

    Default RE: My First Stored Procedure

    The Field "PR_SKU" and the paremeter must be the same type unless you convert one or the other. Also you set a variable "PR_UnitPrice" and then used an undeclared variable "@PR_SKU". I&#039m surprised it doesn&#039t give you an error. If you have Access 2000 run the SP and see what it prompts you for. It should show a prompt for the "@PR_SKU" variable.<BR><BR>Here is my solution to your delima:<BR><BR>Create Procedure "sp_Get_UnitPrice_by_Sku"<BR>(<BR>@pr_sku nvarchar(11)<BR>)<BR>As<BR>SELECT TOP 1 pr_unitprice<BR>FROM products<BR>WHERE pr_sku = @pr_sku<BR><BR>for this to return a only 1 record I used the TOP 1 to return all prices remove that tag. Also the "=" sign will require that those two are exactly the same. If you need more flexibility use the "WHERE pr_sku LIKE &#039%&#039 + @pr_sku + &#039%&#039<BR><BR>to call this SP from ASP use:<BR><BR>strSQL = "sp_Get_UnitPrice_by_Sku &#039" & strSkuVariable & "&#039;"<BR><BR>notice that I had to use a single quote to surround a text variable passed to the stored procedure. and the ";" is an ending tag for a SQL statement.<BR><BR>Good Luck<BR>

Posting Permissions

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