I've read that stored procedures should use output parameters instead of
recordsets where possible for best efficiency. Unfortunately I need to
quantify this with some hard data and I'm not sure which counters to
use. Should I be looking at the SQL Server memory counters or something
else.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
I have written a simple C# console application that create my own Stored Procedures
the code is here
----------------------------------------------------------------------------
static void Main(string[] args)
{
SqlConnection cn;
string strSql;
string strConnection;
SqlCommand cmd;
strConnection="server=(local);database=Northwind;integrated security=true;";
strSql="CREATE PROCEDURE spmahdi @CustomerID nchar(5) @counter int OUTPUT AS SELECT OrderID, OrderDate,RequiredDate,ShippedDate FROM Orders WHERE CustomerID = @CustomerID ORDER BY OrderID SET @counter=@@rowcount";
cn=new SqlConnection(strConnection);
cn.Open();
cmd=new SqlCommand(strSql,cn);
cmd.ExecuteNonQuery();
cn.Close();
Console.WriteLine("Procedure Created!");
}
------------------------------------------------------------------------------------
but it has some errors becuase of my strSql
strSql="CREATE PROCEDURE spmahdi @CustomerID nchar(5) @counter int OUTPUT AS SELECT OrderID, OrderDate,RequiredDate,ShippedDate FROM Orders WHERE CustomerID = @CustomerID ORDER BY OrderID SET @counter=@@rowcount";
I mean in creating the stored procedure
if i delete the Output parameter from my stored procedure
and my strSql would be somethimg like this
strSql="CREATE PROCEDURE spmahdi @CustomerID nchar(5) AS SELECT OrderID, OrderDate,RequiredDate,ShippedDate FROM Orders WHERE CustomerID = @CustomerID ORDER BY OrderID ";
There will be no errors
I use Visual Studio.NET 2003(full versoin)and MSDE(not Sql Server)
Could someone help me solve this problem?
Thanks and Regards.
Hi all,
In my SQL Server Management Studio Express (SSMSE), pubs Database has a Stored Procedure "byroyalty":
ALTER PROCEDURE byroyalty @percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage
And Table "titleauthor" is:
au_id title_id au_ord royaltyper
172-32-1176
PS3333
1
100
213-46-8915
BU1032
2
40
213-46-8915
BU2075
1
100
238-95-7766
PC1035
1
100
267-41-2394
BU1111
2
40
267-41-2394
TC7777
2
30
274-80-9391
BU7832
1
100
409-56-7008
BU1032
1
60
427-17-2319
PC8888
1
50
472-27-2349
TC7777
3
30
486-29-1786
PC9999
1
100
486-29-1786
PS7777
1
100
648-92-1872
TC4203
1
100
672-71-3249
TC7777
1
40
712-45-1867
MC2222
1
100
722-51-5454
MC3021
1
75
724-80-9391
BU1111
1
60
724-80-9391
PS1372
2
25
756-30-7391
PS1372
1
75
807-91-6654
TC3218
1
100
846-92-7186
PC8888
2
50
899-46-2035
MC3021
2
25
899-46-2035
PS2091
2
50
998-72-3567
PS2091
1
50
998-72-3567
PS2106
1
100
NULL
NULL
NULL
NULL
////////////////////////////////////////////////////////////////////////////////////////////
I try to do an ADO.NET 2.0-VB 2005 programming in my VB 2005 Express to get @percentage printed out in the VB Form1. I read some articles in the websites and MSDN about this task and I am very confused about "How to Work with Output Parameters & Report their Values in VB Forms": (1) Do I need the Form.vb [Design] and specify its properties of the object and classes I want to printout? (2) After the SqlConnectionString and the connection.Open(), how can I bring the value of @percentage to the Form.vb? (3) The following is my imcomplete, crude draft code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI;"
Dim connection As SqlConnection = New
SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("byroyalty", connection)
command.CommandType = CommandType.StoredProcedure
...................................................................
..................................................................
etc.
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From the above-mentioned (1), (2) and (3), you can see how much I am lost/confused in attempting to do this task. Please help and give me some guidances and good key instructions for getting the output parameter printed out in the FORM.vb in my VB 2005 Express project.
Thanks in advance,
Scott Chang
Hello all,
I'm writing some stored procedures that first do an Insert or an Update, and then also do a Select to return a Recordset back to an ADO client. However an Insert or Update causes a closed recordset to be produced in ADO. I can skip over the closed recordset with the NextRecordset method.
So the question is, is there any way of stopping the closed recordset being returned? I don't want to have to call the NextRecordset method from ADO as this would mean that the client would need to know about the implementation of the stored procedure.
Has anyone got any ideas on how to get round this?
Thanks
Dave Sykes
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedHello everyone,
I don't know what category would be appropriate for this question but security seems to be close enough.
I have this case scenario: I am running an automated application that extracts data from a web site and stores the data into a table on SQL server 2005. This information is not confidential in the extreme of social insurance #'s, bank account #s, but should not be seen by a typical employee (it has no use for them). After the data has been stored, it retrieves the data from the same table, processes it, and updates the same table. This application runs every hour infinitely.
Should all the insert, update, and select queries be stored under a stored procedure? I am not concern with performance. My concern would fall under design and security.
Is it worth to hide the details of inserting/updating/selecting behind a stored procedure? Or should I just allow the program to send select/update/insert SQL queries?
No employee (other then the developer and the DB admin) or customer ever access this table (They do not have permission from SQL). The username and passwords were created with security in mind.
Any thoughts or ideas?
Thanks for your time, Adrian
Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
Thanks in advance,
Scott Chang
Hello Folks,
Using just SQL (within SQL Server 2000), I'm looking to output 2 (or more) recordsets from a one-to-many join between table all on one row. Help! Can someone please show me how to do this if it can be done.
Below is an example of the situation.
Thanks a lot folks, - Jerry
************* Tables *******************
** ITEMS **
ItemID
=======
10
11
12
** ITEMSTOPARTS **
ItemID ---- PartID ---- Funktion
=================================
10 -------- 100 ------- headlight
10 -------- 120 ------- taillight
** PARTS **
PARTID ---- Manf ---- ProductName
=================================
100 ------- M1 ------ Halogens
120 ------- M2 ------ Red Lights
************* Queries *******************
-- QUERY 1 --
==============
(dashes preserve indentation)
SELECT
- i.itemID AS ItemID,
- p1.partID AS HLPartID,
- p1.manf AS HLManf,
- p1.name AS HLName,
- p2.partID AS TLPartID,
- p2.manf AS TLManf,
- p2.name AS TLName
FROM - ZItems i
- - - - INNER JOIN ZItemsToParts ip
- - - - - - ON i.itemID = ip.itemID AND
- - - - - - (ip.funktion = 'headlight' OR
- - - - - - ip.funktion = 'taillight') AND
- - - - - - i.itemID = 10
- - - - LEFT OUTER JOIN ZParts p1
- - - - - - ON ip.partID = p1.partID AND
- - - - - - ip.funktion = 'headlight'
- - - - LEFT OUTER JOIN ZParts p2
- - - - - - ON ip.partID = p2.partID AND
- - - - - - ip.funktion = 'taillight'
************* Output *******************
Using "Query 1" I get this:
(Note: HL = Headlight; TL = Taillight)
ItemID - HLPartID - HLManf - HLName --- TLPartID - TL Manf - TL Name
================================================== ==============
10 ----- 100 ------ M1 ----- Halogens - NULL ----- NULL ---- NULL
10 ----- NULL ----- NULL --- NULL ----- 120 ------ M2 ------ Red Lights
I wish to output the proceeding two record set in one row
Any ideas how this can be done with SQL Server 2000
(with JOINS in the FROM clause)?
- DESIRED OUTPUT:
ItemID - HLPartID - HLManf - HLName --- TLPartID - TL Manf - TL Name
================================================== =============
10 ----- 100 ------ M1 ----- Halogens - 120 ------ M2 ------ Red Lights
-= End-O-Message =-
Hi,
What is the syntax calling a stored procedure (let say named 'myproc') with OUTPUT variables (lets say the proc has an integer output variable named 'myvar') ?
Thanks,
Dror.
i built a stored procedure with inserting in to customers table.
i have one column with identity.
so want to take that identity column value in the same stored procedure.
so how can i write that procedure with insert in to statements in that stored procedures.
can any one tell me.
also how to get that value in ado.net 2.0.
friends please tell me.
Hi
I've an existing SQL 2000 Stored Procedure that return data in many (~20) output parameters.
I'm starting to use it in a .Net c# application and it seems to insist that I setup all the output parameters:
SqlParameter param = cmd.Parameters.Add("@BackgroundColour",SqlDbType.TinyInt);
param.Direction=ParameterDirection.Output;
even if I only need the value of a single one.
Is this right? Is there a way to avoid coding every one every time?
EXEC('SELECT COUNT(docid) AS Total FROM docs WHERE ' + @QueryFilter)I want to get the cound as an output parameter.I can get output parameters to work only when I dont use EXEC. I need to use EXEC for this case since @QueryFilter gets generated in the stored procedure based on some some other data.How can I get that count using ouput parameter?
View 2 Replies View RelatedHi,
I hope someone can help.
I've written a stored procedure that returns a value via an output parameter.
I'm calling the stored procedure in an sql session is a loop, and it passes the value back correctly the first time, but all subsequent calls the output parameter appears to have the same value. I believe that I'm making some very basic mistake, but I can't work it out.
Here's how I'm calling the stored procedure several times
begin
declare @instrumentid int
exec GetInstrumentId 'OFX,AUDCHF,p,1,2007-03-20 16:54:21.843,2007-06-20,100.1', @instrumentid output;
select @instrumentid
exec GetInstrumentId 'OFX,AUDUSD,c,2,2007-03-20 16:54:21.843,2007-06-20,100.2', @instrumentid output;
select @instrumentid
exec GetInstrumentId 'OFX,AUDCHF,p,3,2007-03-20 16:54:21.843,2007-06-20,100.3', @instrumentid output;
select @instrumentid
end
And this is the start of the stored procedure
Create PROCEDURE [dbo].[GetInstrumentId]
(@Ticket varchar(250), @InstrumentId int output)
AS
If I call the stored procedure once it gives the corect output, if I call it several times the output parameter (@instrumentid ) never changes.
Sean
Hi All,
The following is a code snippit. My main interests are the OUT and OUTPUT parameter keywords. One returns a single value, and the other seemingly a resultset. OUTPUT returns a single value, however OUT seems to return a list of values. Could I please get this confirmed?
Also, I cannot see how the value being returned by OUT is being iterated...
Any help on the obove two matters is appreciated.
Thank You
Chris
BEGIN SNIPPET----------------------------------------------------------------------------------------------------------
--The following example creates the Production.usp_GetList
--stored procedure, which returns a list of products that have
--prices that do not exceed a specified amount.
USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40)
, @MaxPrice money
, @ComparePrice money OUTPUT
, @ListPrice money OUT
AS
SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO
--- USE
DECLARE @ComparePrice money, @Cost money
EXECUTE Production.uspGetList '%Bikes%', 700,
@ComparePrice OUT,
@Cost OUTPUT
IF @Cost <= @ComparePrice
BEGIN
PRINT 'These products can be purchased for less than
$'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
PRINT 'The prices for all products in this category exceed
$'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
----------------------
--- Partial Result Set
----------------------
--Product List Price
---------------------------------------------------- ------------------
--Road-750 Black, 58 539.99
--Mountain-500 Silver, 40 564.99
--Mountain-500 Silver, 42 564.99
--...
--Road-750 Black, 48 539.99
--Road-750 Black, 52 539.99
--
--(14 row(s) affected)
--
--These items can be purchased for less than $700.00.
How many parameters can I use for a Stored Procedure.
Hi all,
Just a quick question regarding the use of stored procedures with parameters.
How do I call a stored procedure with a different number of parameters?
For example if:
new SqlParameter("@SectionID", "3"),
new SqlParameter("@Sessionid", Session["ID"])
SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, sqlParams);
were to change to the following on a different page:
new SqlParameter("@SectionID", "3"),
new SqlParameter("@Sessionid", Session["ID"]),
new SqlParameter("@ExtraVariable", ExtraVariable)
SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, spName, sqlParams);
How would I handle the different number of parameters in the Stored Procedure?
cheers,
Pete
I want to create a stored procedure with SQL Server 6.5 that CAN take 3 parameters, all of which are optional. The declaration for the stored procedure is :
CREATE PROCEDURE BackOrdersII @CustCode varchar(10), @FromDate datetime, @ToDate datetime AS
SELECT * FROM ISO_Details
WHERE DATEDIFF(dd, fldEst_Del_Date, getdate()) > 0 AND CONVERT(char(12),fldActual_Del_Date,3)=`01/01/00` AND CONVERT(char(12),fldEst_Del_Date,3)<>`01/01/00` AND fldDeleted<>1
ORDER BY DATEDIFF(dd, fldEst_Del_Date, getdate()) DESC
How do I formulate the procedure to allow me to select from the table, keeping the current WHERE clause but adding extra items to allow the results to be further filtered depending upon which of the parameters are given to the procedure. Any one, two, or all three parameters could be given.
Sorry if this seems like a simple question but I am only just getting into using stored procedures.
Thanks Rupert
Have looked everywhere and cannot find the answer! So perhaps someone here can answer.
I have an Access 2000 front-end to a SQL Server 2000 database.
I know how to create Stored Procedures that receive parameters; and also how to open a Stored Procedure in the query results window using the DoCmd object. For example,
Application.DoCmd.OpenStoredProcedure "MyProc", acViewNormal, acReadOnly
Does anyone know of a way to pass parameters to Stored Procedures & open the result using the DoCmd object? Without getting a parm input dialog?
Alternatively, does anyone know of a way to open a Stored Procedure in the query results view without using the DoCmd object?
Will appreciate any guidance you can provide! Thanks!
CarlR
I use a stored procedure that is calling several other stored procedurewhich update or append values in several tables. All of them are storedprocedures with input parameters by which they filter rows to be updated orinserted into other tables.Filtration is based on certain actual values on forms (form with severalsubforms).My question is following: How to pass parameters to those stored proceduresthat are triggered by a button?Those stored procedures are not recordset of forms, so I can't pass it usingInput Parameters property of forms (or I can?).Thanks.Zlatko
View 1 Replies View RelatedI wish to ultimately have the content of all stored procedures related to a database in a single ASCII file for review. Is that doable? If so, how?
Thanks,
Peter
I'm using the function below to output all of my stored procedures intoa text file. Fice, except that the output file does not reflect thenames of the stored procedures correctly if the name has been changed.For example, I create a stored procedure named: "sp123" and then renameit to "sp123DELETE" or "sp123 DELETE" or "sp123OLD" or "sp123 OLD" andwhat I end up with is four entries in the output file all having thestored procedure name "sp123."I stop the service and restart before outputting the file.Any help is appreciated.lqFunction ExportSP(myPath As String)Dim objSQLServer As New SQLDMO.SQLServerDim dbs As New SQLDMO.DatabaseDim sp As SQLDMO.StoredProcedureDim sptext As StringobjSQLServer.Connect <Servername>, <Username>, <Password>Set dbs = objSQLServer.Databases(<databasename>)Open myPath For Output As #1For Each sp In dbs.StoredProceduressptext = sp.TextPrint #1, sptext & _vbCrLf & vbCrLf & vbCrLf & _"*******" & _vbCrLf & vbCrLf & vbCrLfNextEnd Function
View 4 Replies View RelatedI am stuck on how to syntactically retrieve an output value (@ProdCount) from a stored procedure. The SPROC works fine: the value of @ProdCount appears correctly in the output window. However, I can't retrieve it in the DAL handler (value remains 0). Does anyone have an idea on how to properly extract the return value. TIA for any pointers.SPROC (abridged): ALTER PROCEDURE and_Store_GetProductsByProdCatID_SortPage (@ProdCatID INT,...@ProdCount INT OUTPUT
)
ASSELECT @ProdCount=(SELECT COUNT(*) FROM and_StoreProduct WHERE ProdCatID= @ProdCatID)DECLARE @SQL nvarchar(4000)SET @SQL = 'WITH tmpProd AS ( SELECT ROW_NUMBER() etc.. )SELECT ProdID, etc..FROM tmpProdWHERE Row BETWEEN etc..ORDER BY etc..'
EXECUTE(@SQL)RETURNDAL handler (abridged): Public Overloads Shared Function GetProductListByCatID(ByVal ProdCatID As Integer, ..., ByVal ProdCount As Integer) As List(Of Product) Dim productList As List(Of Product) = New List(Of Product) Try
Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("conAnders").ConnectionString) Dim cmd As SqlCommand = New SqlCommand("and_Store_GetProductsByProdCatID_SortPage", con) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue("@ProdCatID", ProdCatID) '...
cmd.Parameters.AddWithValue("@ProdCount", ProdCount) ' Output parm. Add dummy value.
Dim objProduct As Product 'Temp Product.
con.Open()
Using myReader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) While myReader.Read() objProduct = New Product() With objProduct .ProdID = myReader.GetInt32(myReader.GetOrdinal("ProdID")) 'etc..
End With
productList.Add(objProduct)
End While Dim tmp As Object = cmd.Parameters("@ProdCount").Value ' <-- Not updated
myReader.Close() End Using End Using Catch ex As Exception Throw ' Pass up the error.
End Try Return productList End Function
Hi
How I building a stored procedure that
return a output parameter and value return that I can
call from other stored procedure or VB ?
thank you in advance
My colleague and I (both are newbie’s to .NET) are divided on whether to use stored procedures or parameters.
His viewpoint is, using stored procedures you are spreading the load i.e. SQL server and web server etc. This is not a good solution because it is not a portable when it has to be relocated.
Is he right?
I thought to avoid SQL injection it is best to use stored procedures but I do see his reasoning as well.
Yazzy is Very confused!!
Thanks in advance for any of your thoughts
How can I pass a name of a table to a stored procedure.
I want to pass the name as a parameter. The table already exists in the db.
After that, I will do
"SELECT .....
FROM @tableparameter"
What is the right way to do that?
Hello,
I seached around for an answer to this question but didn't have much luck. Hopefully someone can help.
I am passing two parameters from a web page to a stored procedure. The first paramater @Field is the name of the field in the database I want to search, the second @Value is the value to seach for. The @Value works fine but the SP does not seem to recongnize the field parameter. I'm not sure if what I am attemping is not supported or wheather I just need to format the @Field in a different manner. The code and stored procedure is below.
Thanks for your help, Gary
Here is the web code:
Dim conMSS As New SqlConnection(ConfigurationSettings.AppSettings("dsnMSS"))
Dim cmdItems As New SqlCommand("DS-SPRS.dbo.s_ItemLookUp", conMSS)
cmdItems.CommandType = CommandType.StoredProcedure
cmdItems.Parameters.Add(New SqlParameter("@Field", SqlDbType.VarChar, 50))
cmdItems.Parameters.Add(New SqlParameter("@Value", SqlDbType.VarChar, 50))
cmdItems.Parameters("@Value").Value = txtValue.Text & "%"
cmdItems.Parameters("@Field").Value = lstField.SelectedValue
conMSS.Open()
dgdItems.DataSource = cmdItems.ExecuteReader
dgdItems.DataBind()
conMSS.Close()
Here is the stored procedure:
CREATE PROCEDURE s_ItemLookUp
@Field AS VARCHAR(50),
@Value AS VARCHAR(50)
AS
SELECT DIV_NO, DIV_NM, LN_NO, LN_DS, ITM_NO, PRD_DS, ITM_MFG_NO, VND_HFC_NM
FROM PRODUCT
WHERE @Field LIKE @Value
ORDER BY DIV_NO, LN_NO, ITM_NO
GO
I am creating a stored Procedure and I am getting an error which relates to DATENAME.
SELECT COUNT(*) AS calls, DATENAME(@varDate, CALLSTARTTIME) AS 'Total Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS='1') AND (CALLSTARTTIME >= @StartDate) AND (CALLENDTIME <=@EndDatesql doesn't like: DATENAME( @varDate, CallStartTime)sql works fine if I change @varDate into 'yy', 'mm', 'dd', or 'wk'Since I do not want to make 5 unique Stored Proc just because of @varDate.....Is there any way to work around this problem?
I need to create a SP that will accept a varying number of input parameters. A form that the user completes has a several controls that serve to narrow the number of records returned. The more parameters given, the fewer rows returned. In the past I have accomplished this by dynamically building an SQL statement. I dosen't appear possible to pass an SQL statement in a variable to a SP. Any help or pointers would be appreciated.
View 1 Replies View RelatedI need to set date parameters within Stored Procedures using a sql 2008 R2, with an access 2007 front end. The procedure needs to allow me to set parameters for a start date and an end date.
View 1 Replies View RelatedI am using SQL Server 2000. I have a table with, say, 20 columns. Ihave one procedure which updates all 20 columns at once, accepting aparameter for each column. However, I want to be able to pass anycombination of parameters and only update those columns if passed. SoI created the sp as something likecreate update_t1(@col1 int = null,@col2 int = null,@col3 int = null,....@col20 int = null)asupdate t1set col1 = @col1,col2 = @col2,col3 = @col3,.....col20 = @col20This way I can explicitly specify columns or not as I choose. Forexample I could call "exec update_t1 @col1 = 23, @col4 = 49" to updateonly the first and fourth column. Of course this will obviouslyupdate the remaining columns to null. Is there any way to identifywithin the procedure which parameters were actually specified? Ican't simply do a null check because the user could be updating thevalue to be null. Is there any way for the procedure to know theexact command that invoked it?For example, if I called "exec update_t1 @col1 = 23, @col4 = 49" Iwould want to know only col1 and col4 were specified. If I called"exec update_t1 @col1 = 23, @col4 = 49, @col17 = null" I would want toknow that col1, col4 and col17 were specified, even though col17 wasset to the default of null.
View 3 Replies View Relatedhere's my code:my $sth = $dbhSQL->prepare('{call proc(?,?,?)}');$sth->bind_param(1,"asd");$sth->bind_param(2,"klm");$sth->bind_param_inout(3,$no_go, 1000);$sth->execute;print "no go = $no_go";while(my @row=$sth->fetchrow_array){print "@row";}$sth->finish;Here's my stored procedure:CREATE PROCEDURE proc@id varchar(50),@iyt varchar(20),@no_go int OUTPUTASSET NOCOUNT ONDECLARE @id_err int,@ans_glue_err intBEGIN TRANSACTIONSELECT user_id FROM myTableWHERE user_id=@id AND iyt=@iytSET @id_err = @@ERRORIF @@ROWCOUNT <> 0BEGINSELECT date,date_mod FROM ans_glueWHERE user_id=@idSET @no_go = 0SET @ans_glue_err=@@ERRORENDELSEBEGINSET @no_go = 1ENDIF @id_err = 0 AND @ans_glue_err = 0BEGINCOMMIT TRANSACTIONENDELSEBEGINROLLBACK TRANSACTIONENDthe procedure runs perfectly in Cold Fusion, returning both recordsetsand output param, but in perl, it won't print the output param and Idon't know how to access the second recordsetHELP!
View 1 Replies View RelatedHiI'm trying to make this to work and need helpHere my SP and I'm building sql with output param.Alter PROCEDURE lpsadmin_getSBWReorderDollars(@out decimal(10,2) output,@sType varchar(20),@dSearchDateFrom datetime,@dSearchDateTo datetime,@sOrderType char(1))ASDECLARE @sql as nvarchar(4000)SELECT @sql = 'SELECT @out = SUM(Price*Quantity)FROM PortraitReOrderOrder jcpreINNER JOIN Orders jcporON OrderID = OrderIDWHERE jcpor.Archive = 0AND jcpre.CreatedDate between ''' + CONVERT(varchar(10),@dSearchDateFrom, 101) + ''' AND ''' + CONVERT(varchar(10),@dSearchDateTo, 101) + ''''IF @sOrderType <> 0SELECT @sql = @sql + ' AND LEFT(OrderType,3) = (SELECT OrderTypeNameFROM OrderTypes WHERE OrderTypeID = ' + @sOrderType + ')'IF @sType = 'Active'SELECT @sql = @sql + ' AND PATINDEX(''%SHR%'', AccessCode) = 0 'IF @sType = 'Shared'SELECT @sql = @sql + ' AND PATINDEX(''%SHR%'', AccessCode) <> 0 'Print @sqlEXECUTE sp_executesql @sqlIt gives me an error messageMust declare the variable '@out'.Please help
View 2 Replies View RelatedI am trying to set up a stored procedure to return details about an item in my database. Say I use the following declarations for a stored procedure:
@Parameter1 int output,
@Parameter2 varchar(200) output,
@Parameter3 int output
and then I used a query like:
select @Parameter1 = table.field1, @Parameter2 = table.field2, @Parameter3 = table.field3 from table where itemID = 7
to populate the output parameters the problem I am running into is that if @Parameter1 is null, then @Parameter2 and @Parameter3 also return as null. But, If i rewrite the declarations to:
@Parameter3 int output,
@Parameter2 varchar(200) output,
@Parameter1 int output
then, with the same query, @Parameter3 and @Parameter2 get the values they should, even if @Parameter1 is null
does anyone have any ideas?
I guess another question is, I am right now in the code itself (not the demonstration here) returning 6 values, since this procedure will only ever return one record, I decided to implement it as output variables, rather than returning a 1 record result set to the application. Am I better off just returning the 1 record result set then parsing it out into variables in the application?
Thanks in advance
-madrak