I Can Return INT From A Store Procedure But Get Error When OUTPUT A Varchar
Mar 21, 2008
My store procedure get the QuestionID (PK) from the page and then it's to return a few varchars but gives me the error that string can't be converted to int.
ALTER PROCEDURE [dbo].[usp_getQuestionsforEditPopulateText]
@QuestionID int,
@QuestionDescription varchar(MAX) OUTPUT,
@Option1 varchar(50) OUTPUT,
@Option2 varchar(50) OUTPUT,
@Option3 varchar(50) OUTPUT,
@Option4 varchar(50) OUTPUT,
@Option5 varchar(50) OUTPUT,
@reference varchar(50) OUTPUT,
@chb1 int OUTPUT,
@chb2 int OUTPUT,
@chb3 int OUTPUT,
@chb4 int OUTPUT,
@chb5 int OUTPUT
As
Set @QuestionDescription =(Select questionDescription from QuestionsBank Where questionID = @QuestionID)
Set @Option1 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 1)
Set @Option2 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 2)
Set @Option3 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 3)
Set @Option4 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 4)
Set @Option5 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 5)
Set @reference = (Select referencedescription from reference where questionID = @QuestionID)
Set @chb1 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 1)
Set @chb2 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 2)
Set @chb3 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 3)
Set @chb4 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 4)
Set @chb5 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 5)
RETURN
This is what the page calls
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection("myconnectionstring ")
Dim cmdUpdate As New Data.SqlClient.SqlCommand("usp_getQuestionsforEditPopulateText", dbConnection)
cmdUpdate.CommandType = Data.CommandType.StoredProcedure
cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionID", Data.SqlDbType.Int))
cmdUpdate.Parameters("@QuestionID").Value = QuestionID
cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionDescription", Data.SqlDbType.VarChar))
cmdUpdate.Parameters("@QuestionDescription").Direction = Data.ParameterDirection.Output
cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option1", Data.SqlDbType.VarChar))
cmdUpdate.Parameters("@Option1").Direction = Data.ParameterDirection.Output
cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option2", Data.SqlDbType.VarChar))
cmdUpdate.Parameters("@Option2").Direction = Data.ParameterDirection.Output
cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option3", Data.SqlDbType.VarChar))
cmdUpdate.Parameters("@Option3").Direction = Data.ParameterDirection.Output
cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option4", Data.SqlDbType.VarChar))
cmdUpdate.Parameters("@Option4").Direction = Data.ParameterDirection.Output
cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option5", Data.SqlDbType.VarChar))
cmdUpdate.Parameters("@Option5").Direction = Data.ParameterDirection.Output
cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@reference", Data.SqlDbType.VarChar))
cmdUpdate.Parameters("@reference").Direction = Data.ParameterDirection.Output
cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb1", Data.SqlDbType.Int))
cmdUpdate.Parameters("@chb1").Direction = Data.ParameterDirection.Output
cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb2", Data.SqlDbType.Int))
cmdUpdate.Parameters("@chb2").Direction = Data.ParameterDirection.Output
cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb3", Data.SqlDbType.Int))
cmdUpdate.Parameters("@chb3").Direction = Data.ParameterDirection.Output
cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb4", Data.SqlDbType.Int))
cmdUpdate.Parameters("@chb4").Direction = Data.ParameterDirection.Output
cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb5", Data.SqlDbType.Int))
cmdUpdate.Parameters("@chb5").Direction = Data.ParameterDirection.Output
'open connection
dbConnection.Open()
'Execute non query
cmdUpdate.ExecuteNonQuery()
'close connection
dbConnection.Close()
View 8 Replies
ADVERTISEMENT
Oct 10, 2007
ex:
myprocedure(@Cusname varchar(50), @Cusid int output)as
Insert into Customer(Cusname) values (@Cusname)SELECT @cusid = @@IDENTITY
i add the query to my adapter called CreatCustomer (@Cusnam,@Cusid)private Merp_CusListTableAdapter _CuslistAdapter = null;protected Merp_CusListTableAdapter Adapter
{
get
{if (_CuslistAdapter == null)
_CuslistAdapter = new Merp_CusListTableAdapter();return _CuslistAdapter;
}
}
Now how i write function in BLL to receive output paramter from creatcustomer function?
View 1 Replies
View Related
Sep 21, 2015
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
EX1 : @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 : @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : @SPToCall : [sp_GetSomeResult]
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
DECLARE @myFinalVarFilledWithCachedOutput
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'
[code]...
View 3 Replies
View Related
Feb 24, 2006
naturale02 writes "Here is my SP:
CREATE procedure test as
select top 10 count (*) as Download, a.item_code as Item_Code, b.item_title as Item_Title
into report..ten
from tbl_statistics_archive a , shabox..tbl_items b
where a.item_class = '10'
and convert(char(10),a.created,111) >= convert(char(10),getdate()-7,111)
and convert(char(10),a.created,111) <= convert(char(10),getdate()-1,111)
and a.item_code = b.item_code and a.item_class = b.item_class
group by a.item_code, b.item_title
order by 1 desc
GO
I have create the table called 'ten', and i schedule this SP, but when i start the job, there was nothing shown on 'ten' table, which part i have been doing wrong, kindly help me.
Thank you"
View 1 Replies
View Related
Apr 26, 2007
Hi All,
I have a Stored procedure as below..
create procedure sp_sample
@name varchar(12)
as
begin
set @name = (select name from mytable where id = 1)
select @name
end
how can i return the varchar value from the above Stored procedure?
I want to capture it in SQLFetch ODBC call.
Thanks in advance!!
vishu
Bangalore
View 16 Replies
View Related
Dec 8, 2005
Can the store procedure can return more than one value? If i need to return 2 values, how to do that??
View 1 Replies
View Related
Sep 12, 2005
Dear All, I read through all the post and flipped through the books but Istill can't find the answer to my problem.I'm inserting a new record via a stored procedure and want to return the idvia scope_identity, which I thought would be preety straight forward.The code I'm using is below and this keeps giving me "Multiple-step OLE DBoperation generated errors. Check each OLE DB status value, if available. Nowork was done."How do I pick the returning id value and could anyone see were I'm goingwrong below.Many thanks for any help you can offer.CREATE PROCEDURE [sp_insert_address]@ADDR_NAME_2 [char](70),@ADDR_NO_3 [char](10),@ADDR_ROAD_4 [char](50),@ADDR_DISTRICT_5 [char](50),@ADDR_TOWN_6 [char](50),@ADDR_BOROUGH_7 [char](50),@ADDR_PCODE_8 [char](12),@addr_id [int] OUTPUTAS INSERT INTO [HEAPADLive].[dbo].[TBL_ADDR]([ADDR_NAME],[ADDR_NO],[ADDR_ROAD],[ADDR_DISTRICT],[ADDR_TOWN],[ADDR_BOROUGH],[ADDR_PCODE])VALUES(@ADDR_NAME_2,@ADDR_NO_3,@ADDR_ROAD_4,@ADDR_DISTRICT_5,@ADDR_TOWN_6,@ADDR_BOROUGH_7,@ADDR_PCODE_8)set @addr_id = scope_identity()GO
View 10 Replies
View Related
May 9, 2008
Dear all,
I have a store procedure called ValidateUser which retrtive an authneticated ID from a database.
This procedure is defined as follow :
Code Snippet
ALTER PROCEDURE [dbo].[ValidateUser]
-- Add the parameters for the stored procedure here
@LoginId int = 0 OUTPUT,
@UserName varchar(50),
@pw varchar(60),
@LineId varchar(16)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- return the User id as validted
SELECT @loginID = Id
FROM [NomosConfig].[dbo].Users
WHERE [UserName] = @UserName
AND [PassWord] = @pw
AND [LineId] = @LineId
-- if no record returns, the request user is not allowed
if @@ROWCOUNT < 1
SELECT @loginID = 0
END
Runing this procesdure alone work well and return correct LoginId
What I would like to do is retrieve that return LoginID inside an other procedure. For that I am doinmg as follow :
Code Snippet
DECLARE @AuthenticatedUserId int;
-- verify that user is authorized to access to PSU login
EXECUTE @AuthenticatedUserId= [ValidateUser]
@UserName ='cal'
,@pw ='cal'
,@LineId='PR495250'
SELECT @AuthenticatedUserId
What is strange is that the @AuthenticatedUserId is always retunring 0 ..
Did I write something wrong ?
thnaks fro help
serge
View 3 Replies
View Related
Jan 24, 2006
I'm trying to use a store procedure to add an item into the database and retrieve the id of such item within and output parameter but this is not happening.
The item is added into the db but the output param is not modified.
Here is the code:
SP
CREATE procedure dbo.AddItem(@Desc nvarchar(100),@intItemID int output)as insert into RR_Item ( desc ) values ( @Desc )
select @intItemID = SCOPE_IDENTITY()GO
I have tried in the last line of the SP
select @intItemID = SCOPE_IDENTITY()
select @intItemID = @@IDENTITY
select @intItemID = max(itemid) from RR_Item
but nothing seems to work.
I'm calling the store procedure as follows from asp.net:
Dim intItemID As New SqlParameter("@intItemID", SqlDbType.Int)
intItemID.Direction = ParameterDirection.Output
SqlHelper.ExecuteDataset(objConn.ConnectionString, "AddItem", desc, intItemID)
MsgBox(intItemID.Value.ToString)
Any help would be appreciated.
Thanks
View 1 Replies
View Related
Jan 23, 2004
How do I call capture the output (not return value) from calling a store procedure from within a store procedure so I can use that data for further processing (say join it with another table)?
For example,
CREATE PROCEDURE dbo.sp_test AS
--- returns all words not in Mastery Level 0
EXEC sp_anothertest
--- use the data coming back from sp_test and join it with another table here and say insert them into tblFinalResults
SELECT * tblFinalResults
GO
Thanks!
View 1 Replies
View Related
Jun 3, 2008
Hi All, I have to return a string value from the store procedure. If condition success BEGIN RETURN 'Success' END ELSE BEGIN RETURN 'Fail' ENDI am retrieving this value with ExecuteReturnQuery() method. But it gives me the error like "Conversion failed when converting the varchar value to data type int."can anyone please help me for this?Thank you.Regards.
View 5 Replies
View Related
Feb 28, 2006
Can store procedure return rows?
If store procedure can return rows, how to use ASP.NET to receive it? thanks!
View 2 Replies
View Related
Aug 14, 2006
How can i do ?
It's always display error : "Can' not convert nvarchar to int"
Thank you very much.
View 8 Replies
View Related
Jun 23, 2006
I have 1 files, one is .sql and another is stored procedure. SQL file will call the stored procedure by passing the variables setup in the SQL file. However, everything ran well except at the end, I try to get the return value from SP to my SQL file ... which will send notification email. But, I get the following msg ... I am not sure how to fix this ... help!!! :(
Syntax error converting the varchar value 'ABC' to a column of data type int.
SQL file
======================
DECLARE @S AS VARCHAR(1000)
EXEC @S = PDT.DBO.SP_RPT 'ABC'
SELECT CONTENT = @S -- this is the value I am trying to pass as content of the email
EXEC PRODUCTION.DBO.SENDEMAIL 'xxx@hotmail.com', 'Notification', @S
======================
Stored Procedure
======================
CREATE procedure sp_RPT( @array varchar(1000) ) AS
DECLARE @content AS VARCHAR(2000)
SET @content = 'RPT: ' + @array + ' loaded successfully '
SET @array = 'ABC'RETURN CONVERT(VARCHAR(1000),@array)
GO
View 2 Replies
View Related
Oct 22, 2005
Hi all,Could someone give me an example on how to create and execute the store procedure with using output parameter?In normal, I create the store procedure without using output parameter, and I did it as follow:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, etc...)ASBEGIN //my codeENDGOWhen i executed it, I would said: Execute NewEmployee 123456789, 'peter mailler', getDate(), etc....For output parameter:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, @message Varchar(40) out)ASBEGIN insert into Employee ...... //if error encountered set @message = "Insertion failure"ENDGOExec NewEmployee 123456789, 'peter mailler', getDate(), do I need to input something for the output parameter here?Anyone could give me an example on how to handle the output parameter within the store procedure coz I am not sure how to handle it?Many thanks.
View 1 Replies
View Related
Sep 20, 2006
I have a sp that will do two select from two table. now, can datareader read both table or only dataset can? if datareader can? how to handle it?
View 1 Replies
View Related
Apr 12, 2005
Does anyone know how to call a SQL store procedure that return a value to the page?
I've a simple data entry aspx page with several textboxes and a save button. When user fill out the form and click save/submit, it calls a store procedure to insert a row into a SQL table and automatically generate an ID that need to return the the page to display for the user.
Are there a similar article somewhere?
Thank you all!
View 6 Replies
View Related
Apr 1, 2008
Hi All,
I have written a stored procedure that has a return value (OUTPUT Parameter) and was wondering if there is any way to retreive this value in SQL Server Reporting Services 2005? I get the result fine, but cannot figure out how to get the return parameter.
Thanks in advance.
Glenn
View 5 Replies
View Related
May 28, 2014
There are about 10 select statements in a store procedure.
All select statements are need.
Is it possible to output only the result of last select statement?
View 2 Replies
View Related
Jan 28, 2008
Hi all,
I've a requirement to store the output of the stored procedure into temp. tables/ table varibles.
I've 4 select statements as my output of the stored procedure. How do I store the results of all the 4 select stmnts into 4 different temp tables.
Simplified SP is as...
Create procedure usp_test
as
begin
select c1,c2 from table1
select c3,4 from table2
select c9,c8 from table3
select c5,c7 from Table4
end
I'm expecting something like this...
declare @table1 table (c1, c2)
insert into @table1
Exec <Sp_Name>
select * from @table1
I know the above stmnt works, if my SP has only 1 select stmnt as output.
Please help me to acheive this for multiple select statements.
Thanks,
View 5 Replies
View Related
Oct 14, 2007
Dear friends,I have a problem with a simple select statement and I don't know why it is happening.I have 2 tables, Fees and FeesDataRoles. Fees presents all the fees and FeesDataRole is a middle table between Fees and Roles table. So each fee can have multiple Roles and a Role can have many Fees.Now I have a select statement:Select *From Fees Inner Join FeesDataRoles ON Fees.FeeID = FeesDataRoles.FeeIDWhere (FeesDataRoles.DataRoleID = @DataRoleID) AND (FeesDataRoles.RecordStatus = 1 ) AND (FeesDataRoles.ValidFrom >= getdate() ) AND ( FeesDataRoles.ValidTo <= getdate() OR FeesDataRoles.ValidTo is null)Now it shouldn't take that long to execute this procedure but surprisingly sometimes when I insert a value to the table and then execute this store procedure it does now show the data just added. Very strange.....!!!!I ran the procedure 5 times after inserting an item and nearly 1 out of 5 does not return the right result righ. ( It does not include the recently inserted rows)Anyone have any idea....?I used Tuning Advisor, no sugestion. I change the clustered index in FeesDataRoles from FeesDataRoleID(the primary key of the table) to DataRoleID to increase the performance, still it happens sometimes.Is my Where clause so costly that cause this problem.Please help. I really appreciate your help.Regards,Mehdi
View 2 Replies
View Related
Jul 22, 2015
I use new query to execute my store procedure but didnt return any value is that any error for my sql statement??
USE [Pharmacy_posicnet]
GO
/****** Object: StoredProcedure [dbo].[usp_sysconf] Script Date: 22/07/2015 4:01:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_sysconf]
[Code] ....
View 6 Replies
View Related
Feb 6, 2008
I am trying to retrieve the Output value from running a Stored Procedure. How can I retrieve the value into a variable?
Below is the source code that I have:1 ' Insert question_text into Question table
2 Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
3 Dim cmd As New SqlCommand
4 Dim QuestionID As Integer
5
6 ' Set connection to DB
7 cmd.Connection = conn
8
9 cmd.CommandText = "usp_QuestionResponse_ins"
10 cmd.CommandType = Data.CommandType.StoredProcedure
11
12 Dim sp1 As SqlParameter = cmd.Parameters.Add(New SqlParameter("@question_id", SqlDbType.Int, 4)).Direction = ParameterDirection.Output
13 cmd.Parameters.Add(New SqlParameter("@assessment_id", AssessmentID))
14 cmd.Parameters.Add(New SqlParameter("@domain_id", DomainID))
15 cmd.Parameters.Add(New SqlParameter("@question_text_en", QuestionTextEn))
16 cmd.Parameters.Add(New SqlParameter("@question_text_sp", QuestionTextSp))
17 cmd.Parameters.Add(New SqlParameter("@category_en", CategoryEn))
18 cmd.Parameters.Add(New SqlParameter("@display_order", DisplayOrder))
19 cmd.Parameters.Add(New SqlParameter("@question_template_id", 3))
20 QuestionID = sp1.Value
21 cmd.Connection.Open()
22 cmd.ExecuteNonQuery()
23
24 conn.Close()
25 cmd.Dispose()
26
Here is the Stored Procedure that I have: 1 ALTER PROCEDURE usp_QuestionResponse_ins
2 (
3 @question_id int = null output,
4 @assessment_id int = null,
5 @domain_id int = null,
6 @question_text_en nvarchar(1000) = null,
7 @question_text_sp nvarchar(1000) = null,
8 @category_en nvarchar(500) = null,
9 @display_order smallint = null,
10 @question_template_id int = null
11 )
12 As
13 BEGIN
14 DECLARE @Err Int
15
16 DECLARE @QuestionID INT
17 DECLARE @ReturnValue INT
18 SET @ReturnValue = 0
19
20 DECLARE @CategoryId int
21 SELECT @CategoryId = NULL
22
23 -- Check to see if an answer has already been inserted (i.e. revisiting)
24 SELECT @CategoryId = category_id FROM category WHERE (category_name = @category_en) AND (active_f = 1) AND (delete_f = 0)
25 IF ( @CategoryId IS NOT NULL )
26 BEGIN
27 EXEC @ReturnValue = usp_question_ins @question_id OUTPUT, @assessment_id, @domain_id, 2, 1, 'Right', @display_order, 1, 8, @CategoryID, @question_text_en, Null, Null, 'Horizontal', 0, Null, Null, 0, 1, 0
28 SET @QuestionID = @ReturnValue
29 END
30
31 IF ( @QuestionID IS NOT NULL )
32 BEGIN
33 DECLARE @question_locale_id int
34 EXEC usp_question_locale_ins @QuestionID, 1, @question_text_en, 1, 0
35 EXEC usp_question_locale_ins @QuestionID, 2, @question_text_sp, 1, 0
36
37 END
38
39 RETURN @QuestionID
40 End
What am I doing wrong? How do I properly capture the QuestionID from the SP? Running the SP from command line works fine.
Thanks
View 2 Replies
View Related
Oct 26, 2000
Hi
How I building a stored procedure that
return a output parameter and value return that I can
call from other stored procedure or VB ?
thank you in advance
View 1 Replies
View Related
Jul 20, 2005
Hi all. I have a stored procedure on my sql server that returns asimple informtation about that tim a database was backed up. I owuldlike to create an HTA that operator types can look at to make surethat the backups finished, can someone help me do this?The stored procedure in called sp_lastback and the output looks likethis:database days since backup timestamp of backupdbase1 0 2004-10-14 00:41:21.000dbase2 0 2004-10-14 00:08:36.000dbase3 1 2004-10-13 22:46:57.000Can this be done? If someone could help me with this simple problem, Icould probably reuse the same method a 100 useful ways.Thanks in advance!
View 3 Replies
View Related
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
Feb 12, 2008
I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.
I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE. It is a OLEB connection.
I have two parameters mapped:
tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO.
returnvalue output long 1
I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.
The first part of the sp is below and I set the value @i and return.
CREATE procedure [dbo].[Marketing_extract_history_load_TEST]
@table_name varchar(200),
@i int output
as
Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.
View 2 Replies
View Related
Jul 20, 2004
I tried to display return value from stored procedure output parameter in Query Analyzer, but I do not know how to do it. Below is my stored procedure:
CREATE PROCEDURE UserLogin
(
@Email nvarchar(100),
@Password nvarchar(50),
@UserName nvarchar(100) OUTPUT
)
AS
SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password
GO
If I run the query in Query Analyzer directly, I can display @UserName:
DECLARE @UserName as nvarchar(100)
SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password
Select @UserName
But how can I display @UserName if I call stored procedure:
exec UserLogin 'email', 'password', ''
I believed it return int, right?
Thanks in advance for help.
View 2 Replies
View Related
Jul 25, 2005
I am trying to insert a record using stored procedure thr' ASP.NET page. I am getting following error. How to find out what exactly is the problem
Line 4: Incorrect syntax near 'l'. The label 'PD' has already been declared. Label names must be unique within a query batch or stored procedure. The label 'BA' has already been declared. Label names must be unique within a query batch or stored procedure. Unclosed quotation mark before the character string ''.
View 1 Replies
View Related
Jul 23, 2005
HiI've the following SP:-----------CREATE PROCEDURE spServicios@numero int,@maxdif int,@resultado int OUTPUTASBEGINSET ROWCOUNT 1UPDATE control SET registro = getdate()WHERE (fecha >= getdate()AND fecha < dateadd(mi, @maxdif, getdate())AND clientes_codigo IN (SELECTclientes_codigo FROM telefonos WHERE numero = @numero)SELECT @resultado = @@rowcountSET ROWCOUNT 0ENDSELECT @resultado------------But when I try to compile I get the message: Incorrect syntax near thekeyword 'SELECT'Any idea ?Thanks in advanceJ
View 2 Replies
View Related
Oct 24, 2005
Hi all,Here is my error: Server: Msg 245, Level 16, State 1, Procedure NewAcctTypeSP, Line 10Syntax error converting the varchar value 'The account type is already exist' to a column of data type int.Here is my procedure:ALTER PROC NewAcctTypeSP(@acctType VARCHAR(20), @message VARCHAR (40) OUT)ASBEGIN --checks if the new account type is already exist IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @acctType) BEGIN SET @message = 'The account type is already exist' RETURN @message END
BEGIN TRANSACTION INSERT INTO AcctTypeCatalog (acctType) VALUES (@acctType) --if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction IF @@error <> 0 OR @@rowcount <> 1 BEGIN ROLLBACK TRANSACTION SET @message = 'Insertion failure on AcctTypeCatalog table.' RETURN @message END ELSE BEGIN COMMIT TRANSACTION END
RETURN @@ROWCOUNTENDGO
--execute the procedureDECLARE @message VARCHAR (40);EXEC NewAcctTypeSP 'CDs', @message;I am not quite sure where I got a type converting error in my code and anyone can help me solve it???(p.s. I want to return the @message value to my .aspx page)Thanks.
View 9 Replies
View Related
Jul 28, 2006
create procedure Pr_addsupportor(
@supportornumber varchar(10),
@company varchar(100),
@title char(10),
@firstname char(30),
@surname char(30),
@addressline1 varchar(1000),
@addressline2 varchar(1000),
@addressline3 varchar(1000),
@postcode varchar(20),
@town char(100),
@county char(100),
@country varchar(100),
@phonenumber varchar(15),
@faxnumber varchar(15),
@email varchar(100),
@paymenttitle varchar(100),
@barcode varchar(2000),
@collector varchar(3),
@status varchar(10),
@registerdate datetime)
INSERT into
supportor(
[supportor number],
company,
title,
[first name],
surname,
[address line1],
[address line2],
[addess line3],
[post code],
town,
county,
country,
[phone number],
[fax number],
[e-mail],
[payment title],
barcode,
collector,
status,
[register date])
values
(
@supportornumber,
@company,
@title,
@firstname,
@surname,
@addressline1,
@addressline2,
@addressline3,
@postcode,
@town,
@county,
@country,
@phonenumber,
@faxnumber,
@email,
@paymenttitle,
@barcode,
@collector,
@status,
@registerdate);
Server: Msg 156, Level 15, State 1, Procedure Pr_addsupportor, Line 22
Incorrect syntax near the keyword 'INSERT'.
plz help me to find the error
View 4 Replies
View Related
Oct 28, 2006
I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)
I have used the old goto method for trapping errors committing or rolling back the transaction.
Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before?
I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp).
Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section?
So how to return timestamp and a return value and how to check for concurrency all in the try/catch.
by the way I read that you could not return an identity in the output clause but I had no problem.
Thanks for help on this
SM haig
View 5 Replies
View Related
