Retrieving Output Parameter From Stored Proc

Oct 2, 2006

I have difficulty reading back the value of an output parameter that I use in a stored procedure. I searched through other posts and found that this is quite a common problem but couldn't find an answer to it. Maybe now there is a knowledgeable person who could help out many people with a good answer.
The problem is that 
cmd.Parameters["@UserExists"].Value evaluates to null. If I call the stored procedure externally from the Server Management Studio Express everything works fine.
Here is my code:using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("mys_ExistsPersonWithUserName", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = userName;
cmd.Parameters.Add("@UserExists", SqlDbType.Int);
cmd.Parameters["@UserExists"].Direction = ParameterDirection.Output;
cn.Open();
int x = (int)cmd.Parameters["@UserExists"].Value;
cn.Close();
return (x>1);
}
 
 And the corresponding stored procedure:
 ALTER PROCEDURE dbo.mys_Spieler_ExistsPersonWithUserName
(
@UserName varchar(16),
@UserExists int OUTPUT
)
AS
SET NOCOUNT ON
SELECT @UserExists = count(*)
FROM mys_Profiles
WHERE UserName = @UserName


RETURN  

View 1 Replies


ADVERTISEMENT

Retrieving Output Parameter From A Stored Procedure

Feb 28, 2008

Lets say I have the following stored procedure

Code:

View 2 Replies View Related

Stored Proc With Varchar Output Parameter

Nov 30, 2004

Hi Guys
I am wondering if you could spare some time and help me out with this puzzle.
I am new to this stuff so please take it easy on me.

I’m trying to create procedure which will take 2 input parameters and give me 1 back.
Originally there will be more outputs but for this training exercise 1 should do.
There are 2 tables as per diagram below and what I’m trying to do is
Verify username & password and pull out user group_name.

|---------------| |-----------------------|
| TBL_USERS | |TBL_USER_GROUPS|
|---------------| |-----------------------|
| USERNAME | /|GROUP_ID |
| PASSWORD | / |GROUP_NAME |
| GROUP_ID |< | |
|---------------| |-----------------------|

For my proc. I am using some ideas from this and some other sites, but obviously i've done something wrong.

'====================================================
ALTER PROCEDURE dbo.try01
(
@UserName varchar(50),
@Password varchar(50),
@Group varchar Output
)
AS
SET NOCOUNT ON;
SELECT TBL_USERS.USERNAME, TBL_USERS.PASSWORD,@Group = TBL_USER_GROUPS.GROUP_NAME,
TBL_USERS.USER_ID, TBL_USER_GROUPS.GROUP_ID
FROM TBL_USERS INNER JOIN TBL_USER_GROUPS
ON TBL_USERS.GROUP_ID = TBL_USER_GROUPS.GROUP_ID
WHERE (TBL_USERS.USERNAME = @UserName)
AND (TBL_USERS.PASSWORD = @Password)
'====================================================


and this is what i'm getting in VS.Net while trying to save.


'====================================================
ADO error: A select statement that assigns a value to variable must
not be combined with data-retrieval operation.
'====================================================


I did not see any samples on the net using ‘varchar’ as OUTPUT usually they where all ‘int’s. Could that be the problem?

Please help.

CC

View 1 Replies View Related

Stored Proc Output Parameter To A Variable

Mar 16, 2006

I'm trying to call a stored procedure in an Execute SQL task which has several parameters. Four of the parameters are input from package variables. A fifth parameter is an output parameter and its result needs to be saved to a package variable.

Here is the entirety of the SQL in the SQLStatement property:
EXEC log_ItemAdd @Destination = 'isMedicalClaim', @ImportJobId = ?, @Started = NULL, @Status = 1, @FileType = ?, @FileName = ?, @FilePath = ?, @Description = NULL, @ItemId = ? OUTPUT;
I have also tried it like this:
EXEC log_ItemAdd 'isMedicalClaim', ?, NULL, 1, ?, ?, ?, NULL, ? OUTPUT;

Here are my Parameter Mappings:
Variable Name Direction Data Type Parameter Name
User::ImportJobId Input LONG 0
User::FileType Input LONG 1
User::FileName Input LONG 2
User::FilePath Input LONG 3
User::ImportId Output LONG 4

When this task is run, I get the following error:


0xC002F210 at [Task Name], Execute SQL Task: Executing the query "EXEC log_ItemAdd @Destination = 'isMedicalClaim', @ImportJobId = ?, @Started = NULL, @Status = 1, @FileType = ?, @FileName = ?, @FilePath = ?, @Description = NULL, @ItemId = ? OUTPUT" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The User::ImportId package variable is scoped to the package and I've given it data types from Byte through Int64. It always fails with the same error. I've also tried adjusting the Data Type on the Parameter Mapping, but nothing seems to work.
Any thoughts on what I might be doing wrong?
Thanks.

View 4 Replies View Related

How To Specifiy An Output Parameter When Calling Stored Proc In C#

Oct 2, 2007

How do I specify a parameter as an output parameter --> OUTPUT paramI am referring to how to do this on line 10 below
1 int GetTheReturnValue=0;2//Code not shown//
9  mySqlCommand.Parameters.Add("@returnParameter", SqlDbType.Int, 10).Value = 0;  // How to specify output param?10 GetTheReturnValue=mySqlCommand.ExecuteNonQuery();

View 7 Replies View Related

Stored Proc - Output Parameter Does Not Work With Nested Level

Apr 26, 2004

Anyone can help with this question: thanks

in a asp .net application, I call a stored procedure which have a output parameter.
the output parameter works find in sql session, but not in the asp .net application.

if I put select msg_out = "error message" in position A(see below for stored proc), it works fine
if I put them inside the if statement, the output parameter wont work in asp .net application, but fine in SQL session
The stored proc was created like this:

Create procedure XXXXXXX
(@msg_out varchar(80) OUTPUT
)
as
begin

while exists (*******)
begin
//position A
if certain condition
begin

select msg_out = "error message"
return 1
end

end


end


end

It seems to me that anything inside if - the second begin...end - it wont get executed.

Anyone has got a clue

Any help much appreciated!

View 5 Replies View Related

Read Rows AND An Output Parameter From Codebehind Returned By Stored Proc?

Feb 5, 2008

I have a stored procedure that returns a resultset AND an output parameter, pseudocode:myspGetPoll@pollID int,@totalvoters int outputselect questionID,question from [myPoll] where pollID=@pollID  @totalvoters=(select count(usercode) from [myPoll] where pollID=@pollID)1. In my code behind I'd like to read both the rows (questionID and question) as well as total results (totalvoters) How could I do so?2. what would be the signature of my function so that I can retreive BOTH a resultset AND a single value?e.g.: private function getPollResults(byval pollID as integer, byref totalvoters as integer) as datasetwhile reader.read    dataset.addrow <read from result>end whiletotalvoters=<read from result>end functionThanks!

View 2 Replies View Related

Trouble Retrieving Value Of Output Parameter

Mar 18, 2008

I am using Asp.net 2.0 and I am trying to retrieve the value from an output parameter in a sql server 2005 stored procedure. The following is my code:
 
Public Function GetUserProfile(ByVal UserID As String, ByVal Name As String) As String
Dim Value As StringDim ValueParam As SqlParameter = New SqlParameter("@Value", SqlDbType.VarChar, 50, ParameterDirection.Output)Dim command As New SqlCommand("sp_GetUserProfile", con)
command.CommandType = CommandType.StoredProcedure
command.Parameters.AddWithValue("@UserID", UserID)command.Parameters.AddWithValue("@Name", Name)
'ValueParam.ParameterName = "@Value"
'ValueParam.SqlDbType = SqlDbType.VarChar
'ValueParam.Direction = ParameterDirection.Output
'ValueParam.Size = 50
'command.Parameters.Add(ValueParam)
'ValueParam.Direction = ParameterDirection.Output
command.Parameters.Add(ValueParam)
 
'TryIf con.State <> ConnectionState.Open Then con.Open()
command.ExecuteNonQuery()Value = command.Parameters("@Value").Value.ToString
If con.State <> ConnectionState.Closed Then con.Close()Return Value
'Catch ex As Exception
'If con.State <> ConnectionState.Closed Then con.Close()
'Return ""
'End Try
End Function
 
 ALTER PROCEDURE dbo.sp_GetUserProfile
(@UserID varchar(36),
@Name varchar(25),
@Value varchar(50) OUTPUT
)
AS
--SET NOCOUNT ON
 SELECT @Value = [Value]
FROM UserProfileWHERE [UserID] = @UserID
AND [Name] = @Name
 
The error I am recieving is :   Procedure or function 'sp_GetUserProfile' expects parameter '@Value', which was not supplied. Can someone tell me what I am doing wrong? Thanks!

View 5 Replies View Related

Retrieving Values From Print Statemens In Stored Proc In Sql Into Asp.net Page

Sep 3, 2007

 Hi allI hv made a stored procedure which printsvarious messages using Print statement(shown in bold)------------------------------------------------------------------------------ .....if (@current_date<@ed) and (@current_date>@sd)    begin    print 'Date Lies Between Boundary Limits'    select * from membership where uid=@uid    end    else    begin    if(@pipe=1)            begin                if(@plan_id=1)                begin                 print 'Monthly Plan Activated'                update membership set   start_date=@opt_sd,end_date=DateAdd(M,1,@opt_sd),status=@opt,pipeline=0,user_option='',plan_id=null,download_limit=20 where uid=@uid                select * from membership where uid=@uid                end                else                    begin                    print 'Weekly Plan Activated'                    update membership set start_date=@opt_sd,end_date=DateAdd(D,7,@opt_sd),status=@opt,pipeline=0,user_option='',plan_id=null,download_limit=10 where uid=@uid                    select * from membership where uid=@uid                    end            end    end --------------Now I want to retrieve the messages disp by these Print statements in my asp.net page where i m calling this stored proc.Pls suggest RegardsMunish 

View 2 Replies View Related

How To Determine If Stor Proc Parameter Is Output Or Input

Jul 23, 2005

I know that you can retrieve whether a parameter is for output buy wayof the "isoutparam" field, but is there anything that tells you whethera parameter is input/output?thanks

View 3 Replies View Related

Retrieving Parameter List For A Stored Procedure

Jul 23, 2004

Is there a way to retrieve the parameter list for a given stored procedure?

I am trying to create a program that will autogenerate a list of stored procedures and their parameters so that changes to the database can be accurately reflected in code.

Thanks,
Allen K.

View 1 Replies View Related

Stored Proc's Using OUTPUT

Jun 16, 2004

How can I view the output of a stored procedure that is returning a OUTPUT variable? I've written a stored proc that uses OUTPUT but when I run it, all I see is "The command(s) completed successfully." I'm at a loss on how to debug/verify/view the output value.

The same thing happens using this example from MS.

CREATE PROCEDURE titles_sum @TITLE varchar(40) = '%', @SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @TITLE
SELECT @SUM = SUM(price)
FROM titles
WHERE title LIKE @TITLE
GO


TIA - KB

View 5 Replies View Related

Using A Cursor Output From Stored Proc

Feb 19, 2005

Has anyone ever tried to use a cursor as an output variable to a stored proc ?

I have the following stored proc - CREATE PROCEDURE dbo.myStoredProc
@parentId integer,
@outputCursor CURSOR VARYING OUTPUT
AS
BEGIN TRAN T1
DECLARE parent_cursor CURSOR STATIC
FOR
SELECT parentTable.childId, parentTable. parentValue
FROM parentTable
WHERE parentTable.parentId = @parentId
OPEN parent_cursor

SET @outputCursor = parent_cursor

DECLARE @childId int
DECLARE @parentValue varchar(50)
FETCH NEXT FROM parent_cursor INTO @childId, @parentValue
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT childTable.childValue
FROM childTable
WHERE childTable.childId = @childId

FETCH NEXT FROM parent_cursor INTO @childId, @parentValue
END

CLOSE parent_cursor
DEALLOCATE parent_cursor
COMMIT TRAN T1
GOAnd, I found that I had to use a cursor as an output variable because, although the stored proc returns a separate result set for each returned row in the first SQL statement, it did not return the result set for the first SQL statement itself.

My real problem at the moment though is that I can't figure a way to get at this output variable with VB.NET.Dim da as New SqlDataAdapter()
da.SelectCommand = New SqlCommand("myStoredProc", conn)
da.SelectCommand.CommandType = CommandType.StoredProcedure
Dim paramParentId as SqlParameter = da.SelectCommand.Parameters.Add("@parentId", SqlDbType.Int)
paramParentId.Value = 1

Dim paramCursor as SqlParameter = daThread.SelectCommand.Parameters.Add("@outputCursor")
paramCursor.Direction = ParameterDirection.OutputThere is no SqlDataType for cursor. I tried without specifying a data type but it didn't work. Any ideas?

Thanks
Martin

View 6 Replies View Related

Output Of A Stored Proc Into A Table

Sep 2, 2004

Want to obtain the outpur of a xp_cmdshell (or any other procedure call) command to a table. Is it possible ?

View 6 Replies View Related

Capture Stored Proc Output To File

Jan 8, 2001

I am running a stored proc that does many updates. I want to capture the results of the stored proc in a text file. Results returned such as, 18 rows affected (the stuff that shows in results pane of query analyzer). Do I have to run the stored proc in batch mode to do this and capture via the output file in OSQL? I also want to capture error messages in case the stored proc aborts in the middle.

I am running SQL Server 7.0 SP2 on Win2K. I am running the stored proc in a DTS package.

Thanks.

Tim

View 3 Replies View Related

Problem In Calling A Stored Proc With OUTPUT Parms

Sep 26, 2000

I have a problem in calling a procedure.
It has two input parameters and seven(7) output parms.

When I run it this way:

exec usp_List_Relationship_Users @relationship_id = 1851 ,
@status = 'Channel Member',
@cust_idOUTPUT,
@user_login_id OUTPUT,
@username OUTPUT,
@password OUTPUT,
@status_id OUTPUT,
@sdesc OUTPUT,
@administrator OUTPUT

I get this error:
Server: Msg 119, Level 15, State 1, Line 10
Must pass parameter number 3 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

But when I run it this way :
exec usp_List_Relationship_Users 1851 ,
'Channel Member',
@cust_idOUTPUT,
@user_login_id OUTPUT,
@username OUTPUT,
@password OUTPUT,
@status_id OUTPUT,
@sdesc OUTPUT,
@administrator OUTPUT

I get the expected results. Why should I omit the name of the name of the input parameters? I don't know why I am getting this error if don't run by ommitting the name of the input params.

Any help regarding this matter is greatlt appreciated.
You can assume that all the variables are declared prior to executing the stored procedure.

Regards
Sushruth Nanduri.

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

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

Stored Proc O/P Parameter

Jan 30, 2002

I have 2 stored proc.Stored proc1(sp1) will call stored proc2(sp2).sp2 will return one output parameter of VARCHAR(5000) to sp1.Sp1 will gets the o/p parameter and stores it to a table.

My problem is while returning sp2 output parameter will truncate the size of the o/p I'm getting a part of it's actaul output.I am using SQL server 2000.How we can solve this truncation?

View 1 Replies View Related

Trigger Error When Inserting Stored Proc Output Into Temp Table.

Feb 18, 2008





I writing a unit test which has one stored proc calling data from another stored proc. Each time I run dbo.ut_wbTestxxxxReturns_EntityTest I get a severe uncatchable error...most common cause is a trigger error. I have checked and rechecked the columns in both of the temp tables created. Any ideas as to why the error is occurring?

--Table being called.


ALTER PROCEDURE dbo.wbGetxxxxxUserReturns

@nxxxxtyId smallint,

@sxxxxxxxxUser varchar(32),

@sxxxxName varchar(32)

AS

SET NOCOUNT ON




CREATE TABLE #Scorecard_Returns

(
NAME_COL varchar(64),
ACCT_ID int,

ACCT_NUMBER varchar(10),

ENTITY_ID smallint,

NAME varchar(100),

ID int,

NUM_ACCOUNT int,

A_OFFICER varchar(30),

I_OFFICER varchar(30),

B_CODE varchar(30),

I_OBJ varchar(03),

LAST_MONTH real,

LAST_3MONTHS real,

IS int

)




ALTER PROCEDURE dbo.ut_wbTestxxxxReturns_EntityTest



AS

SET NOCOUNT ON




CREATE TABLE #Scorecard_Returns

(
NAME_COL varchar(64),
ACCT_ID int,

ACCT_NUMBER varchar(10),

ENTITY_ID smallint,

NAME varchar(100),

ID int,

NUM_ACCOUNT int,

A_OFFICER varchar(30),

I_OFFICER varchar(30),

B_CODE varchar(30),

I_OBJ varchar(03),

LAST_MONTH real,

LAST_3MONTHS real,

IS int

)

INSERT #Scorecard_Returns(

NAME_COL ,

ACCT_ID

ACCT_NUMBER ,

ENTITY_ID,

NAME,

ID,

NUM_ACCOUNT,

A_OFFICER,

I_OFFICER,

B_CODE,

I_OBJ ,

LAST_MONTH

LAST_3MONTHS,

IS

)

EXEC ISI_WEB_DATA.dbo.wbGetxxxxxcardUserReturns

@nId = 1,

@sSUser = 'SELECTED USER',

@sUName = 'VALID USER'

View 4 Replies View Related

Stored Proc Parameter For Table Name

Sep 29, 2004

Recently someone told me that I could use a Parameter in a Stored Proc as a text placeholder in the SQL Statement. I needed to update a table by looping thru a set of source tables. I thought NOW IS MY TIME to try using a parameter as a table name. Check the following Stored Proc

CREATE PROCEDURE [dbo].[sp_Update]
@DistributorID int,
@TableName varchar(50)
AS
UPDATE C
SET C.UnitCost = T.[Price]
FROM (tbl_Catalog C INNER JOIN @TableName T ON C.Code = T.Code)
GO

NEEDLESS TO SAY this didn't work. In reviewing my references this seems to be a no no.

Is it possible to use a parameter as a table name? OR is there another way to do this?

Thanks in advance for your help!

View 3 Replies View Related

Stored Proc With Getdate Parameter

Apr 7, 2004

trying to create SP with parameter and i want to use current date getdate() as parameter.. doesn't seem to work. do i have to use getdate in where clause?

here my SP

CREATE PROC report
(@date datetime)
SET @date = (getdate())-1
as
SELECT..here goes my select statement
where (@date = mydatecolumindatebase)

but im getting error on line 3 and 4
........
Server: Msg 156, Level 15, State 1, Procedure getdatetest, Line 3
Incorrect syntax near the keyword 'SET'.
Server: Msg 156, Level 15, State 1, Procedure getdatetest, Line 4
Incorrect syntax near the keyword 'as'.

View 14 Replies View Related

Problem With Stored Proc With More Than One Parameter

May 27, 2008

Hi!

I'm trying to execute a SP on a SQL Server 2000, using Delphi 2007 (win32) and DBExpress components.

Work on my computer. Don't work on computers without the delphi instaled.
its not a problem with DLLs. All the Necessary DLL are there (and I think that if one was missing, the windows will call for it hauauh)

Midas.dll is inside the apllication and he driver for the SQL Server is there too.

I don't know if this is the corect place to put my problem... But don't can think of other place...

The SP has this:





Code Snippet

IF EXISTS
(
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[ms_TESTE]')
AND
OBJECTPROPERTY(id, N'IsProcedure') = 1
)
DROP PROCEDURE [dbo].[ms_TESTE]
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

CREATE PROCEDURE ms_TESTE
(
@Id varchar(12),
@Nome varchar(500)
)

AS

SELECT *
FROM wtDocAM
WHERE
convert(nvarchar(12), idDOcAM) LIKE @Id
and
nom LIKE @Nome
GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO
I assure that I'm passing both the parameters.

One test was with 8% for the @Id and %a% for Nome.

Anyone have experienced this error?

I´d like to hear a solution if anyone can help me
Thanks in advance!

View 6 Replies View Related

Oracle Stored Proc With OUT Parameter

Nov 30, 2006

Hi,

I am calling an Oracle stored proc which contains an IN and an OUT parameter also.

To the stored proc, I pass two reports parameteres. I get following error when I execute the report:

PLS-00306: wrong number or types of arguments in call to <Procedure name>

Where am I going wrong?

TIA,

Tanmaya

View 3 Replies View Related

Running Stored Proc With Parameter

Sep 3, 2006

hi,

im getting an error when i run the stored proc with a string parameter in execute sql task object.

this is the only code i have:

exec sp_udt_keymaint 'table1'

I also set the 'Isstoredprocedure' in the properties as 'True' though, when you edit the execute sql task object, i can see that this parameter is disabled.

How do i do this right?

cherrie

View 3 Replies View Related

Why Is There A Parameter That Returns An Integer In My Stored Proc?

May 9, 2008



I was comparing the parameters for two stored procs that I made using the SQL Server 2005 express management studio. Both of these sprocs only inserted one field into a single table. These were both of the type varchar.

One of the sprocs had "nocount on" and the other did not. I thought I would see the returns integer parameter in the sproc that did not have "nocount" set to on. I thought this is what returns an integer to validate an insert. Obviously, I am confused about how this works.

Can anyone help me to understand that difference between nocount on and the parameter that returns an integer.

Any help is appreciated.

View 1 Replies View Related

Stored Proc Input Parameter Limited?

Apr 25, 2006

Hi all,

I am trying to create a stored proc that will take
in a long string, but the stored proc does not allow me to take in more
than 50 characters at a time. Is there a way to take away the limit?
Please help me out, thanks in advance.



Daren

View 4 Replies View Related

How To Pass Profile Userid Into Stored Proc As A Parameter

Feb 9, 2008

Hi, I have created an insert stored procedure which inserts a userid into an sql server 2005 field of datatype uniqueidentifier.
What datatype would the parameter be in my c# code?
do i pass it in as an object datatype?
the code is below....
  1 conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);
2
3 cmd = new SqlCommand("spInsKeyswap_history", conn);
4
5 cmd.CommandType = CommandType.StoredProcedure;
6
7 MembershipUser mu = Membership.GetUser(User.Identity.Name);
8
9 object gdUserID = mu.ProviderUserKey;
10
11
12
13
14
15 cmd.Parameters.Add("@user_title", SqlDbType.VarChar, 50);
16
17 cmd.Parameters.Add("@first_name", SqlDbType.VarChar, 20);
18
19 cmd.Parameters.Add("@last_name", SqlDbType.VarChar, 50);
20
21 cmd.Parameters.Add("@email", SqlDbType.VarChar, 50);
22
23 cmd.Parameters.Add("@birthday", SqlDbType.VarChar, 15);
24
25 cmd.Parameters.Add("@alternate_number", SqlDbType.VarChar, 50);
26
27 cmd.Parameters.Add("@msisdn", SqlDbType.VarChar, 50);
28
29 cmd.Parameters.Add("@call1", SqlDbType.VarChar, 50);
30
31 cmd.Parameters.Add("@call2", SqlDbType.VarChar, 50);
32
33 cmd.Parameters.Add("@call3", SqlDbType.VarChar, 50);
34
35 cmd.Parameters.Add("@new_sim_msidn", SqlDbType.VarChar, 50);
36
37 cmd.Parameters.Add("@dealer_id", SqlDbType.UniqueIdentifier);
38
39 cmd.Parameters.Add("@status_code", SqlDbType.Int);
40
41 cmd.Parameters.Add("@support_id", SqlDbType.Int);
42
43 cmd.Parameters.Add("@return_value", SqlDbType.Int);
44
45 cmd.Parameters["@user_title"].Value = txtTitle.Text.ToString();
46
47 cmd.Parameters["@first_name"].Value = txtFirstName.Text.ToString();
48
49 cmd.Parameters["@last_name"].Value = txtLastName.Text.ToString();
50
51 cmd.Parameters["@email"].Value = txtEmailAddress.Text.ToString();
52
53 cmd.Parameters["@birthday"].Value = txtBirthday.Text;
54
55 cmd.Parameters["@alternate_number"].Value = txtAlternate.Text.ToString();
56
57 cmd.Parameters["@msisdn"].Value = txtNew.Text.ToString();
58
59 cmd.Parameters["@call1"].Value = txtCall1.Text.ToString();
60
61 cmd.Parameters["@call2"].Value = txtCall2.Text.ToString();
62
63 cmd.Parameters["@call3"].Value = txtCall3.Text.ToString();
64
65 cmd.Parameters["@new_sim_msidn"].Value = txtOld.Text.ToString();
66
67 //get logged in users user_id from membership
68
69 cmd.Parameters["@dealer_id"].Value = gdUserID;
70
71 cmd.Parameters["@status_code"].Value = 1;
72
73 cmd.Parameters["@support_id"].Value = 0;
74
75
76
77 cmd.Parameters["@return_value"].Direction = ParameterDirection.ReturnValue;
78
79 cmd.ExecuteNonQuery();
80
81 int returnValue = (int)cmd.Parameters["@return_value"].Value;
82
83 conn.Open();
84
85
86
 
 

View 1 Replies View Related

SQL2005 Passing GETDATE() As A Stored Proc Parameter

May 2, 2008

What happened to being able to pass GETDATE() to a stored procedure? I can swear I've done this in the past, but now I get syntax errors in SQL2005.
Is there still a way to call a stored proc passing the current datetime stamp? If so, how?
This code: EXEC sp_StoredProc 'parm1', 'parm2', getdate()
Gives Error: Incorrect Suntax near ')'
I tried using getdate(), now(), and CURRENT_TIMESTAMP with the same result
I know I can use code below but why all the extra code? And, what if I want to pass as a SQL command [strSQL = "EXEC sp_StoredProc 'parm1', 'par2', getdate()" -- SqlCommand(strSQL, CN)]?
DECLARE @currDate DATETIME
SET @currDate = GETDATE()
EXEC sp_StoredProc 'parm1', 'parm2', @currDate
Thanks!

View 5 Replies View Related

Passing Multiple Selections To A Stored Proc Parameter

Jan 11, 2005

Hi,

I am currently in the process of building a stored procedure that needs the ability to be passed one, multiple or all fields selected from a list box to each of the parameters of the stored procedure. I am currently using code similar to this below to accomplish this for each parameter:

CREATE FUNCTION dbo.SplitOrderIDs
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int

SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)

IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)

END
END
RETURN
END
GO


I have it working fine for the single or multiple selection, the trouble is that an 'All' selection needs to be in the list box as well, but I can't seem to get it working for this.

Any suggestions?

Thanks

My plan is to have the same ability as under the 'Optional' section of this page:

http://search1.workopolis.com/jobshome/db/work.search_cri

View 1 Replies View Related

Passing Datetime Variable To Stored Proc As Parameter

Jun 12, 2006

Hello,

I'm attempting to pass a datetime variable to a stored proc (called via sql task). The variables are set in a previous task where they act as OUTPUT paramters from a stored proc. The variables are set correctly after that task executes. The data type for those parameters is set to DBTIMESTAMP.

When I try to exectue a similar task passing those variables as parameters, I get an error:

Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If I replace the 2nd and 3rd parameters with quoted strings, it is successful:
exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1

The stored proc is expecting datetime parameters.

Thanks for the help.

Mike

View 3 Replies View Related

Escaping Single Quote In Stored Proc With Parameter..

Feb 26, 2008

We have a .NET drop down, which gets populated as the user types in letters(last name). If the user types in the single quote we get the error about not escaping the single quote. Question is, which way would it be easier to fix, in the .NET code or in the SQL procedure? I am not to sure if we have full access to the source code since that is a 3rd party control, so if that is not feasible how would I fix that in the stored procedure? This is the current proc that we are using:




Code Snippet
select @str = 'SELECT DISTINCT TOP ' + @Top + ' e.DisplayName
as DbComboText,
e.EmployeeID as DbComboValue
FROM DepartmentDirectory.dbo.Employees ee
INNER JOIN DataMart.dbo.Employees e ON ee.UIN = e.UIN
WHERE e.LastName like ''' + @LastName + ''' AND e.FirstName like ''' + @FirstName + '''
ORDER BY e.DisplayName'




Any help is greatly appreciated.

View 5 Replies View Related

Stored Procedure Output Parameter

Aug 19, 2007

  I have two stored procedures one generates an output parameter that I then use in the second stored procedure.
  1 Try2 Dim myCommand As New SqlCommand("JP_GetChildren", myConn)3 myCommand.CommandType = Data.CommandType.StoredProcedure4
5 myCommand.CommandType = Data.CommandType.StoredProcedure6 myCommand.Parameters.Add(New SqlParameter("@ParentRule", Data.SqlDbType.NVarChar))7 myCommand.Parameters.Add(New SqlParameter("@PlantID", Data.SqlDbType.NVarChar))8 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRulePrefix", Data.SqlDbType.NVarChar))9 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRuleSuffix", Data.SqlDbType.NVarChar))10 myCommand.Parameters.Add(New SqlParameter("@New_PlantID", Data.SqlDbType.NVarChar))11 myCommand.Parameters.Add(New SqlParameter("@New_RuleSetID", Data.SqlDbType.NVarChar))12 myCommand.Parameters.Add(New SqlParameter("@Count", Data.SqlDbType.Int))13 myCommand.Parameters.Add(New SqlParameter("@IDField", Data.SqlDbType.NVarChar))14
15 Dim OParam As New SqlParameter()16 OParam.ParameterName = "@IDFieldOut"
17 OParam.Direction = ParameterDirection.Output18 OParam.SqlDbType = SqlDbType.NVarChar19 myCommand.Parameters.Add(OParam)20
21
22 myCommand.Parameters("@ParentRule").Value = txtParentRule.Text23 myCommand.Parameters("@PlantID").Value = txtStartingPlantID.Text24 myCommand.Parameters("@New_ReleasingRulePrefix").Value = txtReleaseRuleFromPrefix.Text25 myCommand.Parameters("@New_ReleasingRuleSuffix").Value = txtReleaseRuleFromSuffix.Text26 myCommand.Parameters("@New_PlantID").Value = txtEndingPlantID.Text27 myCommand.Parameters("@New_RuleSetID").Value = txtEndingRuleSetID.Text28 myCommand.Parameters("@Count").Value = 129 myCommand.Parameters("@IDField").Value = " "
30 myCommand.Parameters("@IDFieldOut").Value = 031
32 myCommand.ExecuteNonQuery()33
34 Dim IDField As String = myCommand.Parameters("@IDFieldOut").Value35
  If i run this stored procedure in sql it does return my parameter. But when i run this code IDField comes back null. Any ideas

View 6 Replies View Related







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