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
ADVERTISEMENT
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
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
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
Jul 19, 2004
possible to pass in a query as a parameter to a stored proc?
Number of constraints right now would make it a lot easier if I could pass in a query that selects all the ID's, tried but couldn't come up w/anything, just have a simple proc that does the deletes on 1 ID @ a time...since there are up to 100 that will need to be deleted, the qry as a param would be much more convenient. below is the proc...Thx for any help.
Code:
CREATE PROCEDURE [dbo].[s_DeletePeople]
@PeopleID int
/* single id to be deleted, tried just passing
* in a query that resembled a string and using it
* but it didn't work either.
*/
AS
Deletefrom tProjectManager
whereManager_ID in (@PeopleID)
Deletefrom tMerchandiser
whereID in (@PeopleID)
Deletefrom tProjectCall
wheremerchandiser_id in (@PeopleID)
Delete from tManager
whereID in (@PeopleID)
Deletefrom tDistrictManager
whereID in (@PeopleID)
Deletefrom tPeople
whereID in (@PeopleID)
....and on and on
GO
View 2 Replies
View Related
Jan 10, 2007
Guys
I have a simple 'black box' proc deriving start and end dates below (the actual values will be derived from more complex code but here for simplicity are hard coded)
create proc upGetDates
as
declare @start datetime ,
@end datetime
select@start = '2001-01-01' ,
@end = '2007-12-31'
go
I want to reuse this proc many times for different reports and using the date values in the calling sp
an example in pseudo code would be
create proc usedates
as
declare@rstart datetime ,
@rend datetime
exec upGetDates @rstart = @start , @rend = @end -- obtaining dates from ---called 'black box' proc
select * from tablename where datecreated >= @rstart and datecreated < @rend
go
Not sure how to 'trap' the dates from the called proc
Can it be done ?
Thanks in anticipation
View 3 Replies
View Related
Sep 7, 2006
I'm writing a simple voting script and have columns for each options. I need to update the data based on whichever option the user picks.I.e...If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1 Etc., etc.What's the best way to do that without building an ad-hoc SQL statement? There could be many options so I dont want to have lots of redundant SQL statements.Can I just use a varible in a stored proc and do something like this? UPDATE mytable SET @optionUserpicked=@optionUserpicked + 1Thanks in advance
View 2 Replies
View Related
Oct 23, 2007
I have created a stored proc for a report, which works fine. I want to have the user enter a project ID to filter the report, so set the stored proc accordingly. However, I want the user to enter a 10 digit ID, which is the equivilent of two fields in the stored proc. My where statement is :
Where actv.ProjID + '-' + actv.Activity = @project
This works fine under the data tab, I can enter a full project and get the results I want. But I cannot preview the report without getting an error. I'm not sure how to add this to the report parameters, as it is two fields concatenated together. Any help would be appreciated!
View 3 Replies
View Related
Mar 17, 2004
Hi I was hoping that someone might be able to help me with this.
I'm trying to figure out why my VB.net code below generates 0 or 1 but doesn't insert it when I can execute my stored procedure with: exec sp 0
myParm = myCommand.Parameters.Add("@bolProMembCSNM", SqlDbType.Bit)
myParm.Value = IIf(CBool(rblProMembCSNM.SelectedItem.Value) = True, 1, 0)
I've tried everything I used to use with Classic ASP and am stumped now.
Any ideas? I will have to do this for numerous controls on my pages.
Thanks in advance for any advice.
View 4 Replies
View Related
Nov 6, 2002
Hi all,
Is it possible to pass a table variable to a Stored proc or a function?
If it is can you give me the sentax.
TIA,
View 3 Replies
View Related
Jul 30, 2007
Hello,
I have a number of multi-select parameters which I would like to send to a stored procedure within the dataset for use in the stored procedure's IN() statement which in turn is used to filter on or out particular rowsets.
I considered using a hidden string parameter set = " ' " + join(parameter.value, ',') + " ' " so that the hidden parameter would then contain a comma delimiated string of the values selected, which would then be sent on to the stored proc and used in the WHERE clause of one of the queries internal to the stored proc.
But before I start dedicating time to do this I wanted to inquire if anyone here with far more expertise could think of a faster or less system heavy method of creating a single string of comma delimited parameter selections?
Thanks.
View 3 Replies
View Related
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
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
Mar 31, 2004
Hey huys, I got this stored procedure. First of all: could this work?
--start :)
CREATE PROCEDURE USP_MactchUser
@domainUserID NVARCHAR(50) ,
@EmployeeID NVARCHAR(50) ,
@loginType bit = '0'
AS
INSERT INTO T_Login
(employeeID, loginType, domainUserID)
Values
(@EmployeeID, @loginType, @domainUserID)
GO
--end :)
then I got this VB.Net code in my ASP.net page....
---begin :)
Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand
Dim daMatchedUser As SqlClient.SqlDataAdapter
SqlConnection1.Open()
'conn openned
daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1)
daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@domainUserID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters("@EmployeeID").Value = Trim(lblEmployeeID.Text)
daMatchedUser.SelectCommand.Parameters("@domainUserID").Value = Trim(lblDomainuserID.Text)
daMatchedUser.SelectCommand.Parameters("@EmployeeID").Direction = ParameterDirection.Output
daMatchedUser.SelectCommand.Parameters("@domainUserID").Direction = ParameterDirection.Output
SqlConnection1.Close()
'conn closed
End Sub
---
If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?
View 2 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
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
May 8, 2007
Hi, I have found this question asked many times online, but the answers always consisted of getting a parameter from the user and to the report. That is NOT what everyone was asking. I would like to know how to pass a parameter I already have into my stored procedure. Using Visual Studio 2005, in the "Data" tab of a report calling something likeUnique_Login_IPsreturns correctly when that stored proc takes no params, but when I have one that takes the year, for example, I do not know how to pass this info in. Note: the following does not work in this context : EXEC Unique_Login_IPs ParameterValue nor does EXEC Some_proc_without_paramsVisual studio seems to want only the procedure name because it returns errors that say a procedure with the name "the entire line above like EXEC Unique_Login_IPs ParameterValue" does not exist.... Thanks in advance,Dustin L
View 1 Replies
View Related
Apr 25, 2008
I am having a dataentry screen. Some columns i will fill the columns by typing and for some columns i will choose an item from the dropdownlist and i have to send it as an input parameter to stored procedure. How to do that? Pls explain
Thanx
View 3 Replies
View Related
Jul 1, 2004
Hi,
I have a procedure that will save to table in sql server 200 via stored procedure. When I hit the save button it alwasy give me an error saying "Procedure 'sp_AddBoard' expects parameter '@dtmWarrantyStart', which was not supplied" even though I supplied it in the code
which is
Dim ParamdtmWarrantyStart As SqlParameter = New SqlParameter("@dtmWarrantyStart", SqlDbType.datetime, 8)
ParamdtmWarrantyStart.Value = dtmWarrantyStart
myCommand.Parameters.Add(ParamdtmWarrantyStart)
below is the stored procedure.
create Proc sp_AddBoard(@BrandID int,
@strPcName varchar(50),
@bitAccounted bit,
@dtmAccounted datetime,
@dtmWarrantyStart datetime,
@dtmWarrantyEnd datetime,
-- @strDescription varchar(500),
@intStatus int,
@ModelNo varchar(50),
@intMemorySlots int,
@intMemSlotTaken int,
@intAgpSlots int,
@intPCI int,
@bitWSound bit,
@bitWLan bit,
@bitWVideo bit,
@dtmAcquired datetime,
@stat bit output,
@intFSB int) as
if not exists(select strPcName from tblBoards where strPcName=@strPcName)
begin
insert into tblBoards
(BrandID, strPcName, bitAccounted,
dtmAccounted, dtmWarrantyStart,
dtmWarrantyEnd, --strDescription,
intStatus,
ModelNo, intMemorySlots, intMemSlotTaken,
intAgpSlots, intPCI, bitWLan,
bitWVideo, dtmAcquired,intFSB,bitWSound)
values
(@BrandID,@strPcName,@bitAccounted,
@dtmAccounted,@dtmWarrantyStart,
@dtmWarrantyEnd,--@strDescription,
@intStatus,
@ModelNo,@intMemorySlots,@intMemSlotTaken,
@intAgpSlots,@intPCI,@bitWLan,
@bitWVideo,@dtmAcquired,@intFSB,@bitWSound)
end
else
begin
set @stat=1
end
The table is also designed to accept nulls on that field but still same error occured.
Please help
View 1 Replies
View Related
Jul 1, 2005
Hi all,
I had created a stored procedure "DeleteRow" that can receive a parameter "recordID" from the calling function, however, I received a error msg "Procedure or function deleteRow has too many arguments specified." when run the C# code.My code is showing below---------------------------------- --------------------thisConnection.Open();SqlParameter param = DeleteRow.Parameters.Add("@recordI D", SqlDbType.Int, 4); param.Value = key;SqlDataReader reader = DeleteRow.ExecuteReader(CommandBeh avior.CloseConnection) //program stop after runing this lineThe stored procedure code which is showing below:----------------------------------------------------------------CREATE PROCEDURE dbo.deleteRow @recordID INT AS DELETE FROM ShoppingCart WHERE RecordID = @recordIDCan anyone give me some ideal why this happen. Thank alot.wing
View 3 Replies
View Related
Jul 6, 2000
Does anyone know how to pass a list of fields into a stored procedure and then use those fields in a select statement. Here's what I'm trying to do.
I have a front end application that allows the user to pick the fields they want return as a record set. Currently, all this is being done in the application. But, I'd like SQL to do it, if it's possible.
I'd pass the following into a stored procedure.
Set @Fields = "First, Last, ID" -- This is done in the application
Exec usp_return @Fields
Obviously, the following fails stored procedure doesn't work ...
CREATE PROCEDURE @FIELDS varchar(255) AS
SELECT @FIELDS FROM MY_TABLE
~~~~~~~~~~~~~~~
Any ideas?
MAPMAN
View 1 Replies
View Related
Jul 30, 2007
Hi All :)
I have a stored procedure which, initially, I had passed a single parameter into a WHERE clause (e.g ...WHERE CustomerCode = @CustCode). The parameter is passed using a DECommand object in VB6.
I now require the sp to return values for more than one customer and would like to use an IN clause (e.g ...WHERE CustomerCode IN(@CustCode). I know I could create multiple parameters (e.g. ...WHERE CustomerCode in (@CustCode1, @CustCode2,...etc), but do not want to limit the number of customers.
If I set CustCode to be KA1001, everything works fine. If I set CustCode to be KA1001, KA1002 it does not return any records.
I think the problem is in the way SQL Server concatenates the stored procedure before execution. Is what I am attempting to do possible? Is there any particular format I need to set the string parameter to? I've tried:
KA1001', 'KA1002 (in the hope SQL Server just puts single quotes either side of the string)
and
'KA1001', 'KA1002'
Both fail :(
Any ideas?
Regards
Xo
View 11 Replies
View Related
Nov 29, 2005
I am trying to develop a stored procedure for an existing application thathas data stored in numerous tables, each with the same set of columns. Themain columns are Time and Value. There are literally hundreds of thesetables that are storing values at one minute intervals. I need to calculatethe value at the end of the current hour for any table. I am a little newto SQL Server, but I have some experience with other RDBMS.I get an error from SQL Server, complaining about needing to declare@TableName in the body of the procedure. Is there a better way to referencea table?SteveHere is the SQL for creating the procedure:IF EXISTS(SELECTROUTINE_NAMEFROMINFORMATION_SCHEMA.ROUTINESWHEREROUTINE_TYPE='PROCEDURE'ANDROUTINE_NAME='udp_End_of_Hour_Estimate')DROP PROCEDURE udp_End_of_Hour_EstimateGOCREATE PROCEDURE udp_End_of_Hour_Estimate@TableName VarCharASDECLARE @CurrentTime DateTimeSET @CurrentTime = GetDate()SELECT(SELECTSum(Value)* DatePart(mi,@CurrentTime)/60 AS EmissonsFROM@TableNameWHERETime BETWEENDateAdd(mi,-DatePart(mi,@CurrentTime),@CurrentTime)AND@CurrentTime)+(SELECTAvg(Value)* (60-DatePart(mi,@CurrentTime))/60 AS EmissionsFROM@TableNameWHERETime BETWEENDateAdd(mi,-10,@CurrentTime)AND@CurrentTime)
View 15 Replies
View Related
Jul 20, 2005
Hi!I want to pass a column name, sometimes a table name, to a storedprocedure, but it didn't work. I tried to define the data type aschar, vachar, nchar, text, but the result were same. Any one know howto get it work?Thanks a lot!!Saiyou
View 1 Replies
View Related
Aug 7, 2007
I wrote a Stored Procedure spMultiValue which takes Multi Value String Parameter "Month". The stored procedure uses a function which returns a table. when I Execute the stored procedure from SQL Server 2005 with input "January,February,March" everything works fine.
In the dataset , I set command type as Text and typed the following statement.
EXEC spMultiValue " & Parameters!Month.Value &"
its not returning anything.
can anyone tell me how to pass multivalue parameter to stored procedure.
Thanks for your help.
View 2 Replies
View Related
Jan 23, 2008
which is the effective methods of passing more then one parameter to the sql stored procedure from your vb.net windows application
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
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
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
