Runtime Build Sql In Stored Procedures With Output Param Q?

Jul 20, 2005

Hi

I'm trying to make this to work and need help

Here 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)
)
AS
DECLARE @sql as nvarchar(4000)

SELECT @sql = 'SELECT @out = SUM(Price*Quantity)
FROM PortraitReOrderOrder jcpre
INNER JOIN Orders jcpor
ON OrderID = OrderID
WHERE jcpor.Archive = 0
AND jcpre.CreatedDate between ''' + CONVERT(varchar(10),
@dSearchDateFrom, 101) + ''' AND ''' + CONVERT(varchar(10),
@dSearchDateTo, 101) + ''''

IF @sOrderType <> 0
SELECT @sql = @sql + ' AND LEFT(OrderType,3) = (SELECT OrderTypeName
FROM 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 @sql
EXECUTE sp_executesql @sql

It gives me an error message
Must declare the variable '@out'.

Please help

View 2 Replies


ADVERTISEMENT

Output Param && Multiple Recordests From Stored Procedures

Jul 20, 2005

here'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 Related

Output Param With A Stored Procedure

Aug 21, 2001

Hello everyone,

I am working through a tutorial and have stumbled into something that does not quite make sense to me. I was wondering if someone could help me understand this.

I have created this SP, this all makes sense to me due to the assignment of the artistname column value to the @artistname variable. In other words what is on the right of the equal sign is assigned to what is on the left.

create procedure ShowPopStyle
@style varchar(30),
@artistname varchar(30) output
as
select @artistname = artistname
from artists
where style = @style
go

Now when you execute this SP, what does not makes sense to me is if I need to declare a variable to hold the output, which I presume is null, shouldn't the @returnname be on the left side of the equal sign instead of the right?

declare @returnname varchar(30) -- variable for the output from the procedure
exec showpopstyle 'Pop', @artistname = @returnname output
print @returnname

Thanks
Kevin

View 2 Replies View Related

Consuming Stored Procedure Output Param

Sep 14, 2007

This is my SProc:
CREATE PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime
/* Input or Output Parameters *//* Note that if you declare a parameter for OUTPUT, it can still be used to accept values. *//* as is this procedure will very well expect a value for @numberRows */@selectDate datetime ,@selectCountry int ,@numberRows int OUTPUT
AS
SELECT DISTINCT configname FROM ModelRequests JOIN CC_host.dbo.usr_smc As t2 ON t2.user_id = ModelRequests.username JOIN Countries ON Countries.Country_Short = t2.country WHERE RequestDateTime >= @selectDate and RequestDateTime < dateadd(dd,1, @selectDate) AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @selectCountry ORDER BY configname
/* @@ROWCOUNT returns the number of rows that are affected by the last statement. *//* Return a scalar value of the number of rows using an output parameter. */SELECT @numberRows = @@RowCount
GO
And This is my code. I know there will be 100's of records that are selected in the SProc, but when trying to use the Output Parameter on my label it still says -1Protected Sub BtnGetModels_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim dateEntered As String = TxtDate.TextDim selectCountry As String = CountryList.SelectedValue
Dim con As New SqlClient.SqlConnection
con.ConnectionString = "Data Source=10.10;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx"Dim myCommand As New SqlClient.SqlCommand
myCommand.CommandText = "ap_Select_ModelRequests_RequestDateTime"
myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.AddWithValue("@selectDate", dateEntered)
myCommand.Parameters.AddWithValue("@selectCountry", CInt(selectCountry))Dim myParam As New SqlParameter("@numberRows", SqlDbType.Int)
myParam.Direction = ParameterDirection.Output
myCommand.Parameters.Add(myParam)
myCommand.Connection = con
con.Open()Dim reader As SqlDataReader = myCommand.ExecuteReader()Dim rowCount As Integer = reader.RecordsAffected
numberParts.Text = rowCount.ToString
con.Close()
End Sub
 
What should I fix?

View 5 Replies View Related

.NET Framework :: Stored Procedure Can't Return Output Param Value

Oct 8, 2015

My stored procedure was running sqlserver 2012 but can't return output parameter value here i paste my SP ...

alter Procedure BD_InsertMobileClient_GpsLogPlanogram
(
@SalesPersonID int,
@MobileRowID int,
@StatusCode int output
)

[Code] .....

View 3 Replies View Related

Output Param Of Stored Proc - Oledb Command

Jun 5, 2007

Hello

This is my first post so please be gentle!!

I have a stored proc that returns a value:



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET NOCOUNT ON

GO
-- =============================================
ALTER PROCEDURE [dbo].[spPK]
-- Add the parameters for the stored procedure here
@varNSC varchar(4),
@varNC varchar(2),
@varIIN varchar(7),
@varIMCDMC varchar(8),
@varOut as int output
AS
Declare
@varPK int
set @varPK = 0
BEGIN

--This checks Method 1
--NSC = @varNSC
--NC = @varNC
--IIN = @varIIN
begin
if exists
(select Item_id
From Item
Where NSC = @varNSC
and NC = @varNC
and IIN = @varIIN)
set @varPK =
(select Item_id
From Item
Where NSC = @varNSC
and NC = @varNC
and IIN = @varIIN)
set @varOut = @varPK
if @varPK <> 0 Return
end

[There are some more methods here]

Return

END



How do I get at the output value?

I have tried using derived column and ole db command but can't seem to grasp how to pass the value to the derived column. I can get oledb command to run using 'exec dbo.spPK ?, ?, ?, ?, ? output' but don't know what to do from here.

View 10 Replies View Related

Accessing A Stored Procedure From ADO.NET 2.0-VB 2005 Express:How To Define/add 1 Output &&amp; 2 Input Parameters In Param. Coll.?

Feb 23, 2008

Hi all,

In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3":

CREATE PROC [dbo].[spInvTotal3]

@InvTotal money OUTPUT,

@DateVar smalldatetime = NULL,

@VendorVar varchar(40) = '%'



This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in
my SSMSE by using either of 2 sets of the following EXEC code:
(1)
USE AP
GO
--Code that passes the parameters by position
DECLARE @MyInvTotal money
EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%'
PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1)
GO
(2)
USE AP
GO
DECLARE @InvTotal as money
EXEC spInvTotal3
@InvTotal = @InvTotal OUTPUT,
@DateVar = '2006-06-01',
@VendorVar = '%'
SELECT @InvTotal
GO
////////////////////////////////////////////////////////////////////////////////////////////
Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code:


Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form1

Public Sub printMyInvTotal()

Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;"

Dim conn As SqlConnection = New SqlConnection(connectionString)

Try

conn.Open()

Dim cmd As New SqlCommand

cmd.Connection = conn

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "[dbo].[spInvTotal3]"

Dim param As New SqlParameter("@InvTotal", SqlDbType.Money)

param.Direction = ParameterDirection.Output

cmd.Parameters.Add(param)

cmd.ExecuteNonQuery()

'Print out the InvTotal in TextBox1

TextBox1.Text = param.Value

Catch ex As Exception

MessageBox.Show(ex.Message)

Throw

Finally

conn.Close()

End Try

End Sub

End Class
/////////////////////////////////////////////////////////////////////
I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!??
I have 4 questions to ask for solving the problems in this project:
#1 Question: I do not know how to do the "DataBinding" for "Name" in the "Text.Box1".
How can I do it?
#2 Question: Did I set the CommandType property of the command object to
CommandType.StoredProcedure correctly?
#3 Question: How can I define the 1 output parameter (@InvTotal) and
2 input parameters (@DateVar and @VendorVar), add them to
the Parameters Collection of the command object, and set their values
before I execute the command?
#4 Question: If I miss anything in print out the result for this project, what do I miss?

Please help and advise.

Thanks in advance,
Scott Chang



View 7 Replies View Related

How To Debugg Stored Procedures And What Is Runtime Associated With It ?

Mar 7, 2008



Can aybody explain me how to debugg Stored Procedures in SQL 2000 and SQL 2005 ?

What is Runtime associated wih it ?

View 5 Replies View Related

Want To Run Stored Procedures To Build Maintenance Database

May 13, 2015

create all the maintenance scripts (SProcs) to my maintenance databases in an automated way?I have around 12 procedures which I don't want to consolidate in a single Proc and execute.Another problem which is coming to my mind is, I can';t use the below method to execute the proc, as I dont have RDP access to the servers.

sqlcmd -i C:MyFolderMyScript.sql -o C:MyFolderMyOutput.txt

I am thinking to ask for a central location where I can create a folder , dumping all my scripts and build the maintenance DB and creating all my procs, table, etc.

View 1 Replies View Related

Using Stored Procedures To Build The Report Model

Feb 28, 2007

Hi,

I plan to use my stored procedures to build the ad-hoc reporting model

Can someone please tell me where to use the OPENROWSET hack mentioned in this post (below) to create a datasource view for building the Report Model.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=345209&SiteID=1

 

Appreciate your help

Thanks.

 

 

View 1 Replies View Related

What Am I Missing Here? Using A Stored Proc Output To Build Table Rows

Nov 27, 2007

Hi all,

I have access to a stored procedure that was written previously for a process that uses the output from the stored procedure to provide input to a BCP operation in a bat file that builds a flat text file for use in a different system.

To continue with the set up, here is the stored procedure in question:
CREATE PROCEDURE [dbo].[HE_GetStks] AS

select top 15
Rating,
rank,
coname,
PriceClose,
pricechg,
DailyVol,
symbol
from

(selectf.rating,
f.rank,
s.coname,
cast ( f.priceclose as decimal(10,2)) as PriceClose,
cast ( f.pricechg as decimal(10,2)) as pricechg,
f.DailyVol,
f.symbol
from dailydata f, snames s
where f.tendcash = 0
and f.status = 1
and f.typ = 1
and f.osid = s.osid) tt
order by rating desc, rank desc

GO

The code in the calling bat file is:
REM *************************
REM BCP .WRK FILE
REM *************************
bcp "exec dailydb.[dbo].[HE_GetStks]" queryout "d:TABLESINPUTHE_GetStks.WRK" -S(local) -c -U<uname> -P<upass>

This works just peachy in the process for which it was designed, but I need to use the same stored procedure to grab the same data in order to store it in a historical table in the database. I know I could duplicate the code in a separate stored procedure that does the inserting into my database table, but I would like to avoid that and use this stored procedure in case the select statement is changed at some point in the future.

Am I missing something obvious in how to utilize this stored procedure from inside an insert statement in order to use the data it outputs? I know I cannot use an EXECUTE HE_GetStks as a subquery in my insert statement, but that is, in essence, what I am trying to accomplish.

I just wanted to bounce the issue of y'all before I go to The Boss and ask him to change the procedure to SET the data into a database table directly (change the select in the proc to an INSERT to a local table) then have the external BAT file use a GET procedure that just does the select from the local table. This is the method most of our similar jobs use when faced with this type of "intercept" task.

Any thoughts?

View 6 Replies View Related

Access 2007 Runtime: Stored Procedures As Record Source

Feb 15, 2008


Hello

In our company, after switching to Office 2007, Access 2007 runtime is installed on our general user machines.
Everything is just fine and beautiful, except that there is one issue with runtime:

Several forms in my Access Project use stored procedures in SQL Server 2000 as their record source. When i try to open the forms in Access 2007 Runtime on user machines, i receive an error that "The record source dbo.Myprocedure speified on this form or report does not exist ".
I use dbo.ProcName format and the forms open correctly in my full version Access.
What could be the problem?

Thanks!

Elman

View 10 Replies View Related

Stored Procedures With Output Variables

May 25, 2007

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.

View 10 Replies View Related

@@RowCount Output Param

Sep 6, 2007

The following stored procedure sets a value for the @@RowCount global variable.
How do I make use of it in the Data Access Layer?
When I set the SPROC as the source for the object, the value numberRows does not appear to be an option. In the end I just want to set the value of @@RowCount to a Label.Text
What should I do?ALTER PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime
@selectDate datetime
,@selectCountry Int
AS

SELECT DISTINCT configname FROM ModelRequests JOIN
CC_host.dbo.usr_cmc As t2 ON
t2.user_id = ModelRequests.username JOIN
Countries ON
Countries.Country_Short = t2.country
WHERE RequestDateTime >= @selectDate and RequestDateTime < dateadd(dd,1, @selectDate)
AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @selectCountry
ORDER BY configname
SELECT @@RowCount As numberRows

GO 

View 2 Replies View Related

Some Problems With Output Parameters In Stored Procedures

Jul 6, 2004

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.

View 1 Replies View Related

How Output All Stored Procedures To A Table Or File

Feb 22, 2005

I 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

View 4 Replies View Related

Output Stored Procedures As Text Problem

Sep 13, 2005

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 Related

Return BIGINT OUTPUT Param To VB!?

May 9, 2006

Please help me on this one.

I need to return a value to VB.
I've tried returning a numeric value NUMERIC(25,20) via an output parameter but this didn't work. I'm know at a point in wich I created a bigint and multiplied the value so that the decimals are gone. However it only returns NULL?!?!?!?!!?!?
Here's part of my stored proc

CREATE PROCEDURE dbo.uspCalcWeightedAverage @StartDate2 varchar(10), @EndDate2 varchar(10), @InMarket nvarchar(50), @InProductType int, @InWeekDay int, @WeightedAverage bigint OUTPUTAS......SELECT @WeightedAverage = cast(10000000000 * (SUM(HHF.FACTOR) / COUNT(PDF.FLAG)) as bigint)FROM TBL_PRODUCTDEFS PDF INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR] INNER JOIN tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID AND [PDF].[HOUR] = [HHF].[HOUR] AND DATEPART(M,DBI.DATE) = [HHF].[Month]WHERE PDF.MARKETID = @InMarketID AND PDF.PRODUCTTYPEID = @InProductTypeID AND [PDF].[WD-WE] = @InWeekDay AND HHF.MARKETID = @InMarketID AND PDF.FLAG = 1GROUP BY FLAG

When I retrieve the output param it returns a NULL value. the properties in VB say that the parameter has the following props:
attribute 64 (Long)
NumericScale 0 (Byte)
Precision 19 (Byte)
Size 0 (ADO_LNGPTR)
Type adBigInt
Value Null

I try to return it with the following code (got the code from a friend)

Public Function RunProcedure(ByVal v_strStoredProcName As String, ByRef r_varParamValues() As Variant) As ADODB.RecordsetDim objAdoRecordset As ADODB.RecordsetDim objAdoCommand As ADODB.CommandDim lngCtr As Long On Error GoTo RunCommand_Error ' Create cmd object Set objAdoCommand = New ADODB.Command Set objAdoCommand.ActiveConnection = m_oAdoConnection objAdoCommand.ActiveConnection = m_oAdoConnection objAdoCommand.CommandText = v_strStoredProcName objAdoCommand.CommandType = adCmdStoredProc Call objAdoCommand.Parameters.Refresh 'Stop For lngCtr = 0 To UBound(r_varParamValues) If objAdoCommand.Parameters(lngCtr + 1).Direction = adParamInput Then objAdoCommand.Parameters(lngCtr + 1).Value = r_varParamValues(lngCtr) End If Next Set objAdoRecordset = New ADODB.Recordset objAdoRecordset.CursorLocation = adUseClient Set objAdoRecordset = objAdoCommand.Execute 'Stop For lngCtr = 0 To objAdoCommand.Parameters.Count - 1 If objAdoCommand.Parameters(lngCtr).Direction = adParamOutput Or objAdoCommand.Parameters(lngCtr).Direction = adParamInputOutput Then r_varParamValues(lngCtr - 1) = objAdoCommand.Parameters(lngCtr).Value End If Next Set RunProcedure = objAdoRecordsetRunCommand_Exit: ' Collect your garbage here Exit FunctionRunCommand_Error: ' Collect your garbage here Call g_oGenErr.Throw("WeatherFcst.CDbsConn", "RunCommand")End Function


PLEASE HELP.

Regards,

Sander

View 1 Replies View Related

Output Parameters Versus Recordsets In Stored Procedures

Jul 20, 2005

I've read that stored procedures should use output parameters instead ofrecordsets where possible for best efficiency. Unfortunately I need toquantify this with some hard data and I'm not sure which counters touse. Should I be looking at the SQL Server memory counters or somethingelse.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Issues With An Output Param From A Sproc Using SQLDataSource

Nov 28, 2007

I have a stored proc that I'd like to return an output param from. I'm using a SQLDataSource and invoking the Update method which calls the sproc.The proc looks like this currently:
ALTER proc [dbo].[k_sp_Load_IMIS_to_POP_x]@vcOutputMsg varchar(255) OUTPUT
AS
SET NOCOUNT ON ;
select @vcOutputMsg = 'asdf'
 
The code behind looks like this:protected void SqlDataSource1_Updated(object sender, SqlDataSourceStatusEventArgs e)
{
//handle error on return
string returnmessage = (string)e.Command.Parameters["@vcOutputMsg"].Value;
}
 
On the page source side, the params are defined declaratively:
<UpdateParameters>
<asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" />
<asp:Parameter Direction="InputOutput" Name="vcOutputMsg" Type="String" />
</UpdateParameters>
 
 
When I run it, the code behind throws the following exception - "Unable to cast object of type 'System.DBNull' to type 'System.String'"
PLEASE HELP! What am I doing wrong? Is there a better way to get output from a stored proc?

View 3 Replies View Related

SP Syntax Help - Return New Identity Value As Output Param

Feb 4, 1999

I have a stored proc that will insert a new row into a table
with the values of the parameters you pass in. I need it to
return the value of the ID that's generated by an Identity
column once the row has been written and that value has
been generated. If I just do a SELECT Max(), I could
accidentally grab a row written by someone else, right?

My current sp looks like this:

CREATE PROCEDURE sp_SaveNewLabel

-- @LabelID int output
@LabelType int
, @Logo int
, @Field01 char(30)

AS

INSERT INTO tbLabel
(LabelType
, Logo
, Field01)

VALUES
(@LabelType
, @Logo
, @PrintCC
, @Field01)


How do I grab the new LabelID (the column is int, Identity) and return
it from the stored proc. Any help would be greatly appreciated...

Zack

View 2 Replies View Related

Output Param In Oledb Transformation That Calls An Sp

Feb 17, 2008

is it true that I will not be able to use the returned value from an sp that is called on every row from an oledb command transformation? I see all kinds of complaints on the web but cant determine if this would be a waste of time. I'd like to append the returned value (which is calculated and cannot be joined in the buffer) to the data on its way out of the transformation.

View 3 Replies View Related

Casting Float Output Param Throws An Exception.

Jan 9, 2004

I keep getting an exception when trying to cast an output param as a float type. the SPROC is marked as float OUTPUT and the Cost column in the database is a float type as well. And there are no nulls in the cloumn either. here is how my code looks:


SqlParameter prmCost= new SqlParameter("@Cost", SqlDbType.Float,8);
prmCost.Direction=ParameterDirection.Output;
cmd.Parameters.Add(prmCost);

//...blah blah blah

//invalid cast gets throw on here (it happens with all my float types)
productDetails.Cost=(float)prmCost.Value;




Any suggestions as to what I am doing wrong?

View 3 Replies View Related

Learn To Access Stored Procedures With ADO.NET 2.0-VB 2005:How To Work With Output Parameters &&amp; Report Their Values In VB Forms?

Feb 11, 2008

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


View 11 Replies View Related

Error - Output Param - SP - Size Property Has An Invalid Size Of 0

Nov 14, 2007

Using C#, SQL Server 2005, ASP.NET 2, in a web app, I've tried removing the size from parameters of type NCHAR, NVARCHAR, and VARCHAR.  I'd rather just send a string and let the size of the parameter in the SP truncate any extra chars if need be. I began getting the error below, and eventually realized it happened only with output parameters, as in the code snippet below.String[3]: the Size property has an invalid size of 0.   par = new SqlParameter("@BusinessEntity", SqlDbType.NVarChar);  par.Direction = ParameterDirection.Output;  cmd.Parameters.Add(par);  cmd.ExecuteNonQuery();What's the logic behind this? Is there any way around it other than either finding out what the size should be, or assigning a size larger than would ever be needed? ThanksMike Thomas 

View 6 Replies View Related

Doing A SELECT X FROM Y WHERE Z IN (@param) In Stored Procedure?

Jan 21, 2005

Hello,

I have a table with a foreign key field. I need to retrieve all the records where the foreign key matches any of a set. In plain ol' SQL this is accomplished with the IN(a,b,c) statement but I can't get that to work in a stored procedure.

How would I do this? I can imagine that I could parse the input string and create a temporary table and use that to do a join but that seems rather convoluted.

Any tips highly appreciated! If I'm not being clear I'll gladly post more details.

Thanks,

Noc

PS SQL 2000, ASP.NET 1.1, VS 2003.

View 2 Replies View Related

VB.NET Stored Procedure, Can't Pass Param

Jun 18, 2007

Hello,



I have a VB.NET stored procedure as below:




Code Snippet

Partial Public Class StoredProcedures

Public Shared Sub My_UpdateCountsManaged( ByRef paramInOut As Integer)

'here I perform update statement using "paramInOut" passed form calling code

.......

'then I return value to the calling code

paramInOut = 555

End Sub

End Class



Calling code specifies a parameter like this:






Code Snippet

Dim param as Sqlparameter = New SqlParameter("@paramInOut", SqlDbType.Int)

param.Direction = ParameterDirection.InputOutput

param.Value = 999

cmd.Parameters.Add(param)





When I execute the code, it surely gets back "555" from SP, the problem is that SP never gets "999" from calling code despite ParamDirection is InputOutput. It always receives 0. I am afraid I don't understand something fundamental ?

Any help would be appreciated.

Thanks a lot,Fly.

View 4 Replies View Related

How To Pass NULL To SQLdatasource Stored Param

Sep 23, 2007

I'm developing a web app using VS2005.  I have a webpage with panel containing a gridview populated by a SQLdatasource.  The SQLdatasource in turn is populated by a stored procedure that can take up to 5 parameters.  The user types in up to 5 separate words (searchterms) in a text box which are then parsed and passed to the stored proc in the datasource which performs a fulltext search.  The gridview then becomes visible.  My problem is that unless the user types in 5 searchterms (no less), the gridview returns zero rows.  5 searchterms returns the proper results.  Somehow, I need to be able to pass in null or empty values for unneeded parameters.
I've tested the stored procedure in Query Analyzer and from within the SQLdatasource configuration (using Test Query) using 0 up to 5 parameters and it works fine, so that's not my problem.  Here's the code that runs after the user types in their search term(s) and presses a button:Public Sub FTSearch_Command(ByVal sender As Object, ByVal e As CommandEventArgs) Handles btnFullText.Command
Dim x As Integer
pnlFullText.Visible = Falsefiltertext = Replace(txtSearchTxt.Text, "'", "''")
If Not filtertext Is Nothing Then
filtertext = filtertext.Trim
Else
Return
End IfDim arrayString() As String = filtertext.Split(" ")
Dim length As Integer = arrayString.LengthFor x = 0 To (length - 1)
If Not arrayString(x) Is Nothing ThenSelect Case x
Case 0 : lblFTParm1.Text = arrayString(0)Case 1 : lblFTParm2.Text = arrayString(1)
Case 2 : lblFTParm3.Text = arrayString(2)Case 3 : lblFTParm4.Text = arrayString(3)
Case 4 : lblFTParm5.Text = arrayString(4)
End Select
End If
Next
pnlFullText.Visible = "True"
End Sub
Any ideas? 
Thanks in advance.
 

View 2 Replies View Related

How Do I Access The Value Of A Stored Proc Return Param In C# Using ExecuteNonQuery?

Jun 8, 2007

I've got a stored proc to insert a record and return the id of the record inserted in an output param.How do I access this value in my code after the proc is executed?
param = comm.CreateParameter();param.ParameterName = "@MemberID";param.Direction = ParameterDirection.Output;param.DbType = DbType.Int32;comm.Parameters.Add(param);
try{     rowsAffected = GenericDataAccess.ExecuteNonQuery(comm);}catch {     rowsAffected = -1;}
 

View 1 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

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 Related

Transact SQL :: Passing Multiple String Param Values To Stored Proc

Jul 21, 2015

CREATE TABLE Test
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int
);

[Code] ....

Now I have to pass multiple param values to it. I was trying this but didnt get any success

exec
[SP_test]'LOC','LOP'

View 10 Replies View Related

Using Output From A Stored Procedure As An Output Column In The OLE DB Command Transformation

Dec 8, 2006

I am working on an OLAP modeled database.

I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).

I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.

The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.

What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?

I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.

Thanks in advance for any assistance you can provide.

View 9 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved