I'm pretty new when it comes to Stored Procs, and for some reason, the CurAge isn't coming up with an actual numeric value. Does anyone see what I am doing wrong?
CREATE PROCEDURE sp_GetTargetProducts @hmid int, @wtid int AS DECLARE @CurAge INT if exists( SELECT LastName, FirstName, HMID, DATEDIFF (YY , BirthDate, GetDate()) As CurAge FROM Members WHERE hmid = @hmid ) BEGIN SELECT DISTINCT Product, ProductAdvance, AgeBottom, AgeTop, ProductName, ProductDesc, ProductPrice, ProductPicture FROM Products WHERE wtid = @wtid AND AgeBottom <= @CurAge AND AgeTop >= @CurAge GROUP BY Product, ProductAdvance, AgeBottom, AgeTop, ProductName, ProductDesc, ProductPrice, ProductPicture return(1) END else return(0) GO
I've created a sproc in SQL2000 that returns a dataset from a temp table & the number of records it's returning as an output parameter, although I can't seem to retrieve the value it's returning in asp.net although I get the dataset ok.
This is my sproc create procedure return_data_and_value @return int output as set nocount on ... ... select * from #Table select @return = count(*) from #Table drop table #Table go
This is asp.net code
Dim nRecords as Int32 Dim cmd As SqlCommand = New SqlCommand("return_data_and_value", conn) cmd.CommandType = CommandType.StoredProcedure
Dim prm As SqlParameter = New SqlParameter("@return", SqlDbType.Int) prm.Direction = ParameterDirection.Output cmd.Parameters.Add(prm)
I need to create a SQL Server Stored Proc that will handle a variable number of Or conditions. This is currently being done with a MS Access Query as follows
mysql = "UPDATE Table SET Field2 = 1 WHERE " & myw
The above code is very simplified.
I Want to create a stored proc to do this but I cannot send it the SQL to the Stored Proc (or can I) so I need to use parameters instead. I want to do something like
Do until rst.EOF
Set cmd = MakeStoredProc("sp_Table_UpdateField2_ForField1") Set prmField1 = cmd.CreateParameter("Field1", adInteger, adParamInput, , rst!Field2) cmd.Parameters.Append Field1
cmd.Execute
Loop
Again the above is very simplified. So how can you get the the SQL for the Stored Proc for something like the following from a loop
WHERE = (Field1 <> 0) OR (Field1 <> 1) OR (Field1 <> 2) ...
Hey, I create a Select Statement in stored proc and I have printed the variable and it has the correct Select statement. My problem is now that I have the string I want how do I run it. Thanks
two variables declared in my proc:@DATE_RANGE_START as datetime,@DATE_RANGE_END as datetime,When I execute my SP it takes 34 seconds.When I change the variables to:@DATE_RANGE_START1 as datetime,@DATE_RANGE_END1 as datetime,and add this to my sp:declare @DATE_RANGE_START datetimedeclare @DATE_RANGE_END datetimeset @DATE_RANGE_START = @DATE_RANGE_START1set @DATE_RANGE_END = @DATE_RANGE_END1the SP runs in 9 seconds (which is expected)Passing in '1/1/01' and '1/1/07' respectivly.Everything else is equal and non-important to this problem.Why does it take 34 seconds when I use the variables from the inputparameters?Interesting isn't it.Jeff
I haven't been able to get a variable to get its value from a query using other variables as paramters. Is this possible?
Here's my situation:
I have a table workflow ( id int PK, Quarter int UK1, Responsible varchar UK1, Stage varchar UK1 ) The workflowId is a composite key of the other three columns to keep the facttable rows narrow.
And a stored proc GetWorkflowId that looks if a certain combination of quarter, responsible and Stage exists. If so, it returns the id, if not, it inserts the row and returns the Id. So i can;t use a lookup or merge join, becuase the workflow row may not exist yet.
Now i need this workflowId as a variable in my package. (First a sql task uses it to delete old values, then a dataflow task would use it as a derived column to insert the new values.
Quarter is a variable in my package, and i need to lookup/ create a workflowid with the stored proc using Quarter, and then get the return value into a variable WorkflowId. Can i do that?
I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog
EXEC SQL Task Editer settings: RESULTSET: Single Row PARAMETER MAPPING: User::giBatchID DIRECTION: OUTPUT DATATYPE: LONG PARAMETER NAME: 0
PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure
CREATE PROCEDURE CheckCatLog @OutSuccess INT AS
-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON DECLARE @RowCountCAT INT DECLARE @RowCountLOG INT
---these totals should match SELECT @RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT) SELECT @RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG) --PRINT @RowCountCAT --PRINT @RowCountLOG BEGIN IF @RowCountCAT <> @RowCountLOG --PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file' --RETURN 1 SET @OutSuccess = 1 END GO
Hi I wanted to use the table variable in Stored proc , for that i have create the table variable in the main SP which will be used by again called sp(child SPs) now when i am trying to use the same table variable in the child SP, at the time of compliation it is showing error
Msg 1087, Level 15, State 2, Procedure fwd_price_cons, Line 149 Must declare the table variable "@tmp_get_imu_retn".
Can any body give me the idea how to complile the child SP with the same table variable used in the main SP.
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.
Using SQL Server 7 I am trying to modify an existing stored proc and make it more flexible. The below example represents the first part of that proc. The temp table that it should return is then used by another part of the proc (this query represents the foundation of my procedure). I need to figure a way to change the SQL Select statement, choosing between C.CONTRACTCODE and CB.EMPLOYERCODE on the fly. The query below will run but no records are returned. I am starting to believe/understand that I may not be able to use the @option variable the way I am currently.
I've tried creating two SQL statements, assigning them as strings to the @option variable, and using EXEC(@option). The only problem with this is that my temp table (#savingsdata1) goes out of scope as soon as the EXEC command is complete (which means I can not utilize the results for the rest of the procedure). Does anyone know how I can modify my procedure and incorporate the flexibility I've described?
Thanks,
Oliver
CREATE PROCEDURE test @ContractCode varchar(10), @dtFrom datetime, @dtTo datetime, @Umbrella int
AS
declare @option varchar(900)
if @umbrella = 0 set @option = 'c.contractcode' else set @option = 'cb.employercode'
select c.claimsno, c.attenddoctor, c.patientcode, p.sex, cb.employercode into #SavingsData1 from claimsa c inner join Patient p on c.patientcode = p.patientcode inner join claimsb cb on c.claimsno = cb.claimno where @option = @ContractCode and c.dateentered between @dtFrom and @dtTo and c.claimsno like 'P%' and p.sex in('M','F') and c.attenddoctor <> 'ZZZZ'
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?
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
I use SQL Server 2005 and in a Stored Procedure I want to execute a sql statement and assign the result to a variable. How can I do that?The name of the column I want to retreive the value from is "UserID"Here's my SP so far: ALTER PROCEDURE [dbo].[spUnregisterUser] @UserCode int AS BEGIN SET NOCOUNT ON; declare @uid uniqueidentifier --get userid SELECT @uid=UserID FROM tblUserData WHERE UserCode=@UserCode -- Delete user UPDATE tblUserData SET IsDeleted='True' WHERE UserCode=@UserCode END
How do I assign XmlDocument results to a variable (so that I can pass it to a sp)?
I know that the following code works....
select * from tblUsers where userId = 1225 for XML raw
It returns "<row UserId="1225" LastName="Evans" FirstName="Stephanie" MiddleInitial=...."), which is what I want. But when I try to assign it, I get an error "Incorrect syntax near 'XML'."
declare @strXml nvarchar(1000)
set @strXml = (select * from tblUsers where userId = 1225 for XML raw)
I am trying to achieve a seemingly simple task of assigning datetime value to a user variable at the point my package starts running. How can I do this without using a SQL Script Task? Should I be using a script task for this or is there a simpler way to achieving the same thing?
I have a column with int data type.. i am trying to assign this column to a variable ( int) in For Each Loop..but it keeps giving me an erro
The type of the value being assigned to variable "User:ubcontractor_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Am i getting this error becasue the column has NULL value?. how can I resove this probelm?
I have this stored procedure which is supposed to pull the totals of all the responses (Yes) and (No). I created a subquery and was able to get the total of the totals for all the yes's for each Question and date. which is exactly what i want. Only problem is, somewhere in the code, it is now not showing the No's . And i want it to show the no' s too. I also wanted to add another subquery that grabs all the No's and group them by Question and date (just like i did for the yes's). I pretty much want the report to show the total number of yes and no responses to each question for each date. My stored procedure right now only shows the totals for Yes's. Please help.
FROM Qry_Questions INNER JOIN Qry_Sales_Group ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code INNER JOIN ( Select COUNT(qq.response)as TotalYes, Question, Date , response FROM Qry_Questions qq Where qq.response='Yes' GROUP by qq.[Question] , qq.Date,qq.response ) B ON Qry_Questions.Date = B.Date AND Qry_Questions.Question =B.Question and Qry_Questions.response=B.response WHERE Qry_Questions.[Response Type]='YesNo' Group by Qry_Questions.question, Qry_Questions.Date, Qry_questions.Response,B.TotalYes order by Qry_Questions.Question
I'm using a stored procedure to create a table in sql 2000. One of the columns is an identity column. I need to set the seed to a max(number) from a column in another table, this column is not an identity column and can't be changed into one. I've been trying to set the seed by passing a variable. I continue to get errors so either I've got the syntax wrong or it's not possible to set the seed via a variable. Any words of wisdom would be appreciated.
I have this big huge SQL statement. I am trying to see whether there is some quick magic way to assign it to the variable @SQL
Declare @SQL NVARCHAR(MAX) Set @SQL = <????> SELECT ClaimSurgical.FormNbr as ClaimIdentifier,ClaimSurgical.MemberTID, ClaimSurgical.Membernbr as MemberNumber, ClaimSurgical.AdmitDate AS ServiceDateYYYYMMDD, CAST(CAST(ClaimSurgical.AdmitDate AS VARCHAR) AS DATETIME) ServiceDate,ClaimSurgical.ProviderNbr as ClaimPRVNO, ClaimSurgical.VendorNbr as ClaimTaxID, ClaimSurgical.AssignedProvider1 as PCPPRVNO,ClaimSurgical.CAPVendor as PCPTaxID, EventCode_1.EventCodeTypeID,
Hi this is probably a very stupid question, but I still need to know.
How do I set the result of a 'SELECT' statement to a variable? I know I can use CURSOR, but I am certain the SELECT statement will return either 1 record or NULL. Can I use something else apart from CURSOR?
I want to use a variable in a number of SQL statements as paramenter. But I am not sure how to assign the value I want to the variable. The value I want is the maximum week number from one of my tables. I know how to put this value into a new table (which is one field and One row), but I cannot see how to pass this value to my variable. Using the table in my SQL is not straight forward unfortunately, at least not for me.
Greeting. I have a dataset (ORDER BY Column1) like the following in the data flow task pipeline
Column1 Column2
1 A 1 B 2 C 2 F 3 T 3 R 3 X 4 M
I only need data of 1, A;
2, C;
3, T 4, M That is when Column1 value changes. I think we can store the column1 value in a variable as a previous row value. Then I can compare the variable value with the current row value and see if the column1 value changes. Can anyone please help me how to implement this? Or if there is other way to implement it
I've got this query inside a Sql Task against a Excel connection and I'd like to insert that value into a user variable called "Proyecto". How do I such thing?
