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
ADVERTISEMENT
Jul 5, 2007
I have this block of code that is supposed to go out to a SQL2k5 Express DB, get the number value from a record based on a request.querystring containing the table ID number.I get the request.querystring and store it in a variable. By nature this is stored as a String data type.Then I try to convert the string into an integer using both the "Convert.ToInt32(IDNum)" and the "Int32.TryParse(Request.QueryString("ID"), IDNum)" methods.The IDNum variable is to be used to inject into a stored proc as shown below. conn = New SqlConnection(ConfigurationManager.ConnectionStrings("database_connection").ConnectionString)conn.Open()cmd = New SqlCommand("GetMyNumber", conn)cmd.CommandType = Data.CommandType.StoredProcedurecmd.Parameters.Add("@MyNumber", Data.SqlDbType.Int).Value = IDNumSession("EndResult") = cmd.ExecuteScalarcmd.Dispose()conn.Dispose()As you can see the result is to be stored into a session variable to be used later.My query works just fine when created as an actual query and I supply the numerical value of the record. So I know that's working. I know it is connecting to the db based on the debugging I've done. I just, for some reason, cannot get that data type to convert. So my questions are:1. How do I convert the queried ID number to an integer so that I can use it in my stored proc?2. In the end, can the result be stored in the session variable as I have shown or are the session variables only for string types?
View 4 Replies
View Related
Feb 25, 2008
I am using SQL Server 2005 Developer on XP Home.
I have the following in one "New Query" window.
use test_01;
go
create table test_tbl_01 (
fname nvarchar(30),
lname nvarchar(30),
phone nvarchar(30),
another_field nvarchar(30));
go
insert into test_tbl_01 (fname, lname, phone, another_field)
values ('Susan', 'Johnstone', '555-0123', 'Some other data');
insert into test_tbl_01 (fname, lname, phone, another_field)
values ('Steven', 'Stonewall', '555-0124', 'More filler data');
insert into test_tbl_01 (fname, lname, phone, another_field)
values ('Bob', 'Otherguy', '555-0125', 'Just some text');
insert into test_tbl_01 (fname, lname, phone, another_field)
values ('Fred', 'Johnson', '555-6666', 'What ev');
insert into test_tbl_01 (fname, lname, phone, another_field)
values ('Carol', 'Jackson', '555-5432', 'You go girl!');
go
create procedure test_sp_01 @LName nvarchar
as
select *
from test_tbl_01 tt
where tt.lname like @LName + '%';
go
exec test_sp_01 @LName = 'John';
The result is:
Susan Johnstone 555-0123 Some other data
Fred Johnson 555-6666 What ev
Carol Jackson 555-5432 You go girl!
Why does the result include Carol Jackson? I expect that it should only include the two John....
More importantly, how can I get it to inlude only Susan Johnstone and Fred Johnson?
Thanks,
Gregory
View 3 Replies
View Related
Nov 27, 2006
Currently I have the following stored procedure which simply adds a new row in my SQL Express 2005. What I want is that -1). before inserting the record find out the new ID (primary key) value. (ID is automatically a sequential integer generated by SQL Server)2). and set COMPANY_ID = (new) ID Any thoughts? Thanks ALTER PROCEDURE usp_tbl_Company_Insert @Company_ID int, @Name varchar(200), AS<FIND THE NEW ID of the new row in the database> @Company_ID = (new ID) INSERT INTO tbl_Company (Company_ID, Name,)VALUES (@Company_ID, @Name)
View 1 Replies
View Related
Jan 8, 2004
I have a stored proc that inserts into a table variable (@ReturnTable) and then ends with "select * from @ReturnTable."
It executes as expected in Query Analyzer but when I call it from an ADO connection the recordset returned is closed. All the documentation that I have found suggests that table variables can be used this way. Am I doing somthing wrong?
View 1 Replies
View Related
Feb 22, 2006
If I run this statement in Query Analyzer, it properly returns 1for my testing table. But if I put the statement into a storedprocedure, the stored procedure returns NULL. What am I doingwrong? I suspect it may be related to how I defined the parametersfor the stored procedure. Perhaps my definition of TableName andColumnName don't match what COLUMNPROPERTY and OBJECT_ID expect toreceive, but I don't know where to look for the function declarationsfor those. Any pointers would be appreciated.Select statement:SELECT COLUMNPROPERTY(OBJECT_ID('Table1'), 'TestID', 'IsIdentity') ASIsIdentityTable definition:CREATE TABLE [dbo].[Table1] ([TestID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]Stored Procedure definition:CREATE PROCEDURE spTest(@TableName varchar,@ColumnName varchar)AS SELECT COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName,'IsIdentity') AS IsIdentity
View 2 Replies
View Related
Sep 17, 2007
I'm having problem on trying to execute a query in stored procedure that has parameters as a integer. The parameter with the integer is in the WHERE clause. If I take out the WHERE clause, it would work. If I take out the parameter and replace it with a value, it would work. I have try using the CONVERT function to convert it to an integer, still no luck.
Error: Unterminated String Constant.
What is the problem?
Set @strSQL='Select * From(SELECT Row_Number() Over(Order By ' + @SortExpression + ') as Row_Count,Rank() Over (Order By ' + @SortExpression + ') as TableInfo_ColumnSort,dbo.EVENT_LOGS.EVENTLOG_ID, dbo.USERS.USERNAME, dbo.EVENT_LOGS.ITEM_TYPE, dbo.EVENT_LOGS.SCREEN_ID, dbo.EVENT_LOGS.CHANGE_TYPE, dbo.EVENT_LOGS.IP_ADDRESS, dbo.EVENT_LOGS.CREATE_DATE,dbo.USERS.FIRST_NAME,dbo.USERS.Last_NAMEFROM dbo.EVENT_LOGS INNER JOINdbo.USERS ON dbo.EVENT_LOGS.USER_UID = dbo.USERS.USERID) as TableInfoWhere Row_Count Between ' + @startRowIndex + ' and ' + @maxRowIndex + ' ';Exec(@strSQL);
View 3 Replies
View Related
Feb 19, 2007
I'd like to pass a multi-value parameter to a stored proc to satisfy an integer field lookup.
example -
CREATE PROC SSRSQuery
@InPublicationId VARCHAR(500) = NULL AS
SELECT * from Table where PublicationId IN (@InPublicationId)
where PublicationId is defined as an int
I've seen various posts on how to split up the input string parameter to use in a string-based lookup but nothing useful for an integer-based lookup.
Any tips or tricks for this?
View 3 Replies
View Related
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
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
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
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
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
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
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
View Related
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
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
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
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
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
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
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
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
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
Feb 21, 2008
I have quite a few tables which allow NULL values. I have to struggle a lot with DBnull Exceptions :|example: col1,col2,... are all columns of type Integer and possibly NULL. var query = from person in table select new { person.col1, test = (int?) person.col2, person.col3, person.col4, ...}; As soon as my result encounters a DBNull value.. the whole query fails. This is really bad.How would I return the valid values.. and set the keys where there is no value to a null type? (e.g. int -> 0)I tried using "(int?)" but I'm not *really* sure what it does :-) Anyway.. it has no effect :-)
View 1 Replies
View Related
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
Nov 13, 2004
I want to dynamically build a page for a given stored proc.
Given the name of the sproc how can i return the names, datatypes & sizes of the parameters ?
I then needs to find the fields in the result set to generate the page.
Any ideas ?
View 4 Replies
View Related
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
Aug 8, 2002
i want to use store procedure in select query statement. store procedure will take two parameters from table and return one parameter.
for example i want to use
select p1 = sp_diff d1,d2 from table1
sp_diff is stored procedure
d1,d2 value from table
p1 is the returning value
View 1 Replies
View Related
Feb 11, 2015
I have a multiselect checkbox list in my UI. I pass the list items to my stored proc as comma separated parameter and then I have a function which converts this parameter to a table with separate rows.
E.g. : a,b,c,d
Converted to result table
result
a
b
c
d
I want to insert each row of the result table into another table. How to do that.
E.g., the table after the function is :
CREATE TABLE #result
(
Subject varchar(100)
)
insert into #result values ('a')
insert into #result values ('b')
insert into #result values ('c')
insert into #result values ('d')
So the pseudo code is something like
for each row in #result
insert row into another table
View 9 Replies
View Related
Feb 13, 2008
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
View 3 Replies
View Related
Jan 29, 2008
I have cascading parameters in my report, and the vast majority of the fields on which the parameters are
based are varchar fields. In each of the drop-down menus on the report, I would like to have an option
at the top of the list called <All> which allows the user to select all of the possible values.
I have done this successfully for all the varchar fields, but there is one parameter which is based on an
integer. This obviously means it won't accept the value <All>. So, as a temporary workaround I have
created an option called -1, which when selected by the user, selects all the possible values in the
list. However, this is likely to be confusing for users if they don't realise that the -1 option means
"all". Is there any way I can make the -1 actually appear in the list as <All>? How would I go about
this?
The available values for the parameters come from an underlying dataset query.
View 1 Replies
View Related
Jul 3, 2007
Hi have a problem to solve and I hope that this is not a SSRS Bug.
I created a Reports(using SQL Server Project) which has several parameters which values are passed to a SP.
One of these parameter is an Integer and it is an optional value, so if the user fill it is used by the SP, otherwise the SP uses NULL and run anyway.
I starts to define tha parameter:
Datatype = integer
Allow blank value
Available: Non queried
Default: Null
if I want to Preview the report I have to provide an integer to the parameter's field ...
If for instance I set:
Default: Not queried = 0
In the moment I deploy and I use the ReportViewer in my window application the parameter's field is unabled!!
So I tried this solution:
Datatype = integer
Allow blank value
Allow null value
Available: Non queried
Default: Null
In the preview the checkbox: NULL is checked and I click on the View Report.
But when I deploy it,in the ReportViewer in my window application the parameter's field this checkbox is unchecked.
Do I forget something during my setting??I have to control it programmatically??
N.B. By default the user will not user this parameter so the best is that he can click directly on "View Report" without any additional "work" on the parameter!!
Thank you for any help!
View 1 Replies
View Related
