Stored Procedure With Variable As Only Parameter In Where Clause
Hello,
I want to execute a sproc where the query statement goes something along these lines:
SELECT * FROM myTable WHERE @aVarCharVariable
@aVarCharVariable contains column names and their possible values
How do I achieve this?
Cheers!
/Eskil
View Complete Forum Thread with Replies
Related Forum Messages:
Using A Stored Procedure Variable To Define A Where Clause
Hi all, I'm trying to build a Where clause in a stored procedure based on the information that is passed into the stored procedure. Because I don't know how many items will be passed into the stored procedure, I'm having to split the string on a specific character and build the Where clause based on how many strings are found. However, when I try to execute the Where clause it throws an error. Code Snippet ALTER PROCEDURE getUsersAddress -- Add the parameters for the stored procedure here @LName varchar(1000) @City varchar(1000), @State varchar(1000), @License varchar(1000) AS declare @Count as int declare @x as int declare @wLName as varchar(2000) declare @wCityas varchar(2000) declare @wStateas varchar(2000) declare @wLicense as varchar(2000) BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; if right(rtrim(@LName),1) <> ';' begin set @LName = @LName + ';' end set @Count = PATINDEX('%;%',@LName) set @wLName = '(tblUsers.LName = ''' while @Count <> 0 begin set @wLName = @wLName + left(@LName, @Count - 1) set @LName = stuff(@LName, 1, @Count, '') set @Count = PATINDEX('%;%',@LName); if @Count <> 0 begin set @wLName = @wLName + ''') OR (tblUsers.LName = ''' end else begin set @wLName = @wLName + ''')' end end print cast(@wLName as varchar(5000)) -- Insert statements for procedure here SELECT tblUsers.FullName, tblUsers.Addy1, tblUsers.Addy2, tblUsers.City, tblState.StateAbbr, tblUsers.Zip, tblUsers.Zip4 FROM tblUsers INNER JOIN tblState ON tblUsers.FK_StateID = tblState.StateID INNER JOIN tblUserDetails ON tblUsers.UserID = tblUserDetails.FK_UserID WHERE (@wLName) OR (tblUsers.City = @City) OR (tblState.StateAbbr = @State) OR (tblUserDetails.CDLType = @License) END So when I print do an exec getUsersAddress 'Johnson;Smith', 'City;Test City', 'TX;OK', 'Class A;Class B' @wLName comes out as (tblUsers.LName = 'Johnson') OR (tblUsers.LName = 'Smith') However, I can't save the procedure as it gives me an error of: An expression of non-boolean type specified in a context where a condition is expected, near 'OR'. When I copy and paste the @wLName value in place of @wLName in the Where clause it works, so how can I get the @wLName variable to work in that Where clause?
View Replies !
Stored Procedure Parameter And IN Clause
This works: WHERE ltrim(str((DATEPART(yyyy, dbo.Media_Tracking_Ad_History.ADDATE))) IN ('2003','2004','2005')) This doesn't: WHERE WHERE ltrim(str((DATEPART(yyyy, dbo.Media_Tracking_Ad_History.ADDATE))) IN (@strYears)) @strYears will work if I only pass a single value such as 2003. I've tried every combination of single and double quotes I can think of to pass multiple values but nothing works. Any suggestions?
View Replies !
Passing A Stored Procedure Parameter Into An IN Clause
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 Replies !
Reading An OUTPUT Parameter From A Stored Procedure Into A Variable
Hello, I am struggling with this, having read many msdn articles and posts I am non the wiser. I have a sproc with an output parameter @regemail. I can call the sproc OK from my code as below, via a tableadapter. And the sproc works as desired from management studio express. I want to get the result returned in the OUTPUT parameter (NOT the Return Value) and place it in a variable. Can anyone advise how I can do this? J. THE CODE I USE TO CALL THE SPROC Dim tableAdapter As New DataSet1TableAdapters.RegistrationTableAdaptertableAdapter.SPVerifyUser(strRegGuid, String.Empty) THE STORED PROCEDURECREATE Proc [prs_VerifyUser @regid uniqueidentifier,@regemail nvarchar(250) OUTPUT ASBEGIN IF EXISTS(SELECT RegEmail from dbo.Registration WHERE RegID = @regid) BEGIN SELECT @regemail = RegEmail FROM Registration WHERE RegID = @regid Return 1 END Return 2 END
View Replies !
Passing SSIS Package Variable To Stored Procedure As Parameter
I've created a varible timeStamp that I want to feed into a stored procedure but I'm not having any luck. I'm sure its a simple SSIS 101 problem that I can't see or I may be using the wrong syntax in Execute SQL Task Editor I have conn type -- ole db connection -- some server sql source type -- direct input sql statement -- exec testStoredProc @timeStamp = ? if I put a value direclty into the statement it works just fine: exec testStoredProc '02-25-2008' This is the syntax I found to execute the procedure, I don't udnerstand few things about it. 1. why when I try to run it it changes it to exec testStoredProc @timeStamp = ? with error: EXEC construct or statement is not supported , followed by erro: no value given for one or more requreid parameters. 2. I tired using SQL commands exec testStoredProc @timeStamp and exec testStoredProc timeStamp but nothing happens. Just an error saying unable to convert varchar to datetime 3. Also from SRS I usually have to point the timeStamp to @timeStamp and I dont know how to do that here I thought it was part of the parameter mapping but I can't figure out what the parameter name and parameter size should be; size defaults to -1. Thank you, please help.
View Replies !
Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi I am ramesh here from go-events.com I am using sql mail to send out emails to my mailing list I have difficulty combining a select statement with a where clause stored in a variable inside a cursor The users select the mail content and frequency of delivery and i deliver the mail I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them. Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code that does not work For example DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary How do I combine the select statement with the where clause? Help me...I need help urgently
View Replies !
SSIS: Problem Mapping Global Variables To Stored Procedure. Can't Pass One Variable To Sp And Return Another Variable From Sp.
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes. Here are the task steps. [Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step. [Execute SQL Task] - Log an entry to a table indicating that the import has started. [Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works. [Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile. If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS. If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post. Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction. CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate] /* The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate. If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset. Otherwise it returns a FALSE value in the IsNewFile column. Example: exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0 */ @ProcessName varchar(50) , @FileCreateDate datetime , @IsNewFile bit OUTPUT AS SET NOCOUNT ON --DECLARE @IsNewFile bit DECLARE @CreateDateInTable datetime SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName) BEGIN -- The process exists in tbl_ImportFileCreateDate. Compare the create dates. IF (@FileCreateDate > @CreateDateInTable) BEGIN -- This is a newer file date. Update the table and set @IsNewFile to TRUE. UPDATE tbl_ImportFileCreateDate SET FileCreateDate = @FileCreateDate WHERE ProcessName = @ProcessName SET @IsNewFile = 1 END ELSE BEGIN -- The file date is the same or older. SET @IsNewFile = 0 END END ELSE BEGIN -- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE. INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate) VALUES (@ProcessName, @FileCreateDate) SET @IsNewFile = 1 END SELECT @IsNewFile The relevant Global Variables in the package are defined as follows: Name : Scope : Date Type : Value FileCreateDate : (Package Name) : DateType : 1/1/2000 IsNewFile : (Package Name) : Boolean : False Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings. General Name = Compare Last File Create Date Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate. TimeOut = 0 CodePage = 1252 ResultSet = None ConnectionType = OLE DB Connection = MyServerDataBase SQLSourceType = Direct input IsQueryStoredProcedure = False BypassPrepare = True I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate. SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output Parameter Mapping Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1 Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1 Result Set is empty. Expressions is empty. When I run this in debug mode with this SQL statement ... exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output ... the following error message appears. SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning. Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Compare Last File Create Date Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "MyPackage.dtsx" finished: Failure. When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure. When I run this in debug mode with this SQL statement ... exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output ... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message. SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning. Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Compare Last File Create Date Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "MyPackage.dtsx" finished: Failure. The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives? The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing. Thanks for your help.
View Replies !
Stored Procedure With User!UserID As Parameter, As Report Parameter?
I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure. Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? Thanks
View Replies !
Stored Parameter Not Returning Variable
/******** Doesn't matter, solved the problem. It was finding a null value in @DBNAME *********/ If anyone could help me with this it would be very appreciated as I pulling my hair out. This stored procedure is to check each 'site' for more than one admin. If there is more than one admin then it is to append a string variable, @SITES_LIST, with the cursor's current variables. Code: alter procedure [dbo].[LOG_MOD_REPORT_ZERO_ADMINS] --@LIST VARCHAR(7000) OUTPUT AS BEGIN TRY BEGIN TRANSACTION DECLARE @SITES_LIST VARCHAR (7000) --HOLD THE SITE DETAILS FOR THE TEXT FILE SELECT @SITES_LIST = 'SITE_ID,PRACTICE_NAME,DBNAME' + CHAR(10) +CHAR(13) --HEADINGS FOR REPORT DECLARE C CURSOR FOR SELECT ID, PRACTICE_NAME, DBNAME FROM SITE_DETAILS OPEN C DECLARE @SITE_ID int DECLARE @PRACTICE_NAME VARCHAR (50) DECLARE @DBNAME VARCHAR (64) FETCH NEXT FROM C INTO @SITE_ID, @PRACTICE_NAME, @DBNAME WHILE @@FETCH_STATUS = 0 BEGIN --print @SITE_ID --PRINT @PRACTICE_NAME if (SELECT COUNT(ID) FROM PCS_ROLE WHERE PCS_ROLE_NAME IN ('System Administrator', 'Caldicott Lead') AND MAIN_SITE_ID = @SITE_ID) >= 0 Begin -- PRINT 'Entered if statement' SET @SITES_LIST = (@SITES_LIST + convert(varchar,@SITE_ID) + ',' + @PRACTICE_NAME + ',' + @DBNAME + CHAR(10) +CHAR(13)) end Print @SITES_LIST FETCH NEXT FROM C INTO @SITE_ID, @PRACTICE_NAME, @DBNAME END CLOSE C DEALLOCATE C COMMIT TRANSACTION END TRY BEGIN CATCH CLOSE C DEALLOCATE C IF @@TRANCOUNT > 0 ROLLBACK SELECT ERROR_MESSAGE() as ERRORMESSAGE; END CATCH Using print statements I know the cursor is getting the site_details with no problems and entering the if statement, and the select statement does work, so I am guessing it is not setting the variable @SITE_LIST properly. I can append to site list if I just add a string to it, and it prints successfully. However, when I try to perform @SITE_LIST = @SITE_LIST + variables the @SITE_LIST variable is set to '' first, so all i get is the appended variables as print output. Anyone know how to append to a variable like this or concatenate it without resetting itself?! Thanks for any future help, Mike
View Replies !
IN Clause In The Stored Procedure
I am doing something like this: idlist is the list of id's(intergers) create proc spTest(@idlist varchar(1000))asbeginselect * from stuwhere id in (@idlist)end exec spTest '1,2,3' But I am getting an error saying that cannot convert a varchar to int. I think its just some syntax that I am missing. Any clues on doing this??
View Replies !
Stored Procedure With 'TOP' Clause
I'm trying to create a stored procedure which has the 'TOP' clause, in SQL Server 2000.The syntax is CREATE PROCEDURE SPGetRemainingRecordsB @Remain int AS exec('SELECT TOP' + @remain + 'logdetailid FROM boxdetail WHERE logdetailid in (SELECT TOP' + @remain + 'logdetailid FROM boxdetail ORDER BY logdetailid Desc) ORDER BY logdetailid ASC') GO Syntax check is ok,but i get an error "The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified
View Replies !
Stored Procedure Where Clause
I have an existing query from MS Access that I want to convert it toSQL Server Stored Proc. My problem is on how to convert the WHEREclause.This is the query from MS Access:SELECT SchYrSemCourseJoin.SchYrSemCourseID, Students.IDNo, [LastName]& ", " & [FirstName] & " " & [MiddleName] AS Name,Program.ProgramTitle, Program.ProgramDesc, SchYrSem.SchYr,SchYrSem.Sem, SchYrSem.Year, SchYrSem.Section AS Section1,Major.Major, Course.CourseCode, Course.CourseTitle, Course.Unit,SchYrSemCourseJoin.Final, SchYrSem.SchYrSemIDFROM (Program INNER JOIN Students ON Program.ProgramID =Students.ProgramID) INNER JOIN ((Major INNER JOIN SchYrSem ONMajor.MajorID = SchYrSem.MajorID) INNER JOIN (Course INNER JOINSchYrSemCourseJoin ON Course.CourseID = SchYrSemCourseJoin.CourseID)ON SchYrSem.SchYrSemID = SchYrSemCourseJoin.SchYrSemID) ONStudents.IDNo = SchYrSem.IDNoWHERE ((([LastName] & ", " & [FirstName] & " " &[MiddleName])=[Forms]![Rating Report Dialog]![SubName]) AND((SchYrSem.Year) Like IIf(IsNull([Enter Value]),"*",[Enter Value])));This is a stored proc that I have currently created:CREATE PROCEDURE dbo.Rating@LastName nvarchar(50)AS SELECT SchYrSemCourseJoin.SchYrSemCourseID, Students.IDNo,[LastName] + ', ' + [FirstName] + ' ' + [MiddleName] AS Name,Program.ProgramTitle, Program.ProgramDesc, SchYrSem.SchYr,SchYrSem.Sem, SchYrSem.Year, SchYrSem.Section AS Section1,Major.Major, Course.CourseCode, Course.CourseTitle, Course.Unit,SchYrSemCourseJoin.Final, SchYrSem.SchYrSemIDFROM (Program INNER JOIN Students ON Program.ProgramID =Students.ProgramID) INNER JOIN ((Major INNER JOIN SchYrSem ONMajor.MajorID = SchYrSem.MajorID) INNER JOIN (Course INNER JOINSchYrSemCourseJoin ON Course.CourseID = SchYrSemCourseJoin.CourseID)ON SchYrSem.SchYrSemID = SchYrSemCourseJoin.SchYrSemID) ONStudents.IDNo = SchYrSem.IDNoWHERE ((([LastName] + ', ' + [FirstName] + ' ' +[MiddleName])=@LastName)) ReturnGOMy problem is on how can I add the second criteria which is the FieldYear on my stored proc. The query above (MS Access) returns all therecords if the Parameter Enter Value is null.Anyone know how to do this in stored proc? I want to create a storedproc that will have the same results as the query above.Thanks in advance.
View Replies !
Help With WHERE Clause In Stored Procedure
Hi,I have an sp with the following WHERE clause@myqarep varchar(50)SELECT tblCase.qarep FROM dbo.tblCaseWHERE dbo.tblCase.qarep = CASE @myqarep WHEN '<All>' THENdbo.tblCase.qarep ELSE @myqarep@myqarep is returned from a combo box (ms access)...the user eitherpicks a qarep from the combo box or they leave the default which is'<All>'they problem i'm having is that if the record's value fordbo.tblCase.qarep is null...the record does not show up in theresults...but i need it toany help is appreciated.thanksPaul
View Replies !
Stored Procedure Using IN Clause
hi i'm new to this so if i'm missing something please go easy on me!! i'm using access97 and sql server 7 i have a stored procedure that i want to pull back a list of details, to do this i have constructed a sql statement which uses the in clause ie select * from tblx where tblx.strname in (xxxxx) i have created and declared a variable called strName so my statement now reads .... select * from tblx where tblx.strname in (@strName) .... can i pass accross many values in the @strName variable?? - there might be one value there might be twenty - i know using vba how to put the values into my pass through query (which calls the sp), but i can't get the syntax right for sql server to accept this as more than one value (it works fine with a single value) can any one help - if not i might have to go back to linked tables again which i was trying to escape from thanks mike
View Replies !
Use A Stored Procedure In A Where Clause
I'm trying to write a stored procedure that uses a second stored procedure in its where clause. I have a stored procedure that accepts two parameters and outputs a float. What I'd like to do is have a stored procedure that accepts one parameter and has a select statement such as: Select * from table WHERE STOREDPROCEDURE(@param1,table.field)>5 If anyone can give me some advice I'd apprectaite it. Thanks
View Replies !
Use A Stored Procedure In A Where Clause
I'm trying to write a stored procedure that uses a second stored procedure in its where clause. I have a stored procedure that accepts two parameters and outputs a float. What I'd like to do is have a stored procedure that accepts one parameter and has a select statement such as: Select * from table WHERE STOREDPROCEDURE(@param1,table.field)>5 If anyone can give me some advice I'd apprectaite it. Thanks
View Replies !
Stored Proc Output Parameter To A Variable
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 Replies !
Dynamic Where Clause In Stored Procedure
Hi, I have several parameters that I need to pass to stored procedure but sometimes some of them might be null. For example I might pass @Path, @Status, @Role etc. depending on the user. Now I wonder if I should use dynamic Where clause or should I use some kind of switch, maybe case and hardcode my where clause. I first created several stored procedures like Documents_GetByRole, Documents_GetByRoleByStatus ... and now I want to combine them into one SP. Which approach is better. Thanks for your help.
View Replies !
Dynamic WHERE Clause To Stored Procedure
Hi all! I need to create a stored procedure with a parameter and then send a WHERE clause to that parameter (fields in the clause may vary from time to time thats why I want to make it as dynamic as possible) and use it in the query like (or something like) this: --------------------------------------------------- @crit varchar(100) SELECT fldID, fldName FROM tblUsers WHERE @crit ---------------------------------------------------- Of course this does not work, but I don't know how it should be done, could someone please point me in the right direction on how to do this kind of queries. cheers! pelle
View Replies !
Like '%abc%' Clause In Stored Procedure Problem?
I write a stored procedure as: select * from tableName where firstName like '%' + @keywords + '%' (assuming @keywords is declared with varchar) when I use QA, it runs perfect and returns something that has words in between for matching up firstName, but when I use with the following code (Data access layer) it wouldn't return.. it will only return the matched text.. (ex. if i input 'ke', it suppose return kelvin, kelly, okey something like that, but somehow it only retunrs the whole words that's matched) Is there something wrong? The code for DAL is as follows. Public Function GetOrderList(ByVal keywords As String) As DataSet Dim myConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim myCommand As SqlDataAdapter = New SqlDataAdapter("sp_GetList", myConn) myCommand.SelectCommand.CommandType = CommandType.StoredProcedure Dim paramKeywords As SqlParameter = New SqlParameter("@keywords", SqlDbType.NVarChar) paramKeywords.Value = keywords myCommand.SelectCommand.Parameters.Add(paramKeywords) Dim myDS As New DataSet myConn.Open() myCommand.Fill(myDS) myConn.Close() Return myDS End Function
View Replies !
Help With Dynamic Where Clause In Stored Procedure
I have a stored procedure being called based on user search criteria. Some, the colour and vendor fields are optional in the search so i do not want that portion of the procedure to run. at this point i keep getting errors in the section bolded below it never seems to recognize anything after the if @myColours <> 'SelectAll' CREATE Procedure PG_getAdvWheelSearchResults3 ( @SearchDiameter NVarchar( 20 ), @SearchWidth NVarchar( 20 ), @minOffset int , @maxOffset int , @boltpattern1 NVarchar( 20 ), @VendorName NVarchar( 40 ), @myColours NVarchar( 40 ) ) As BEGIN TRANSACTION SELECT *, dbo.VENDORS.*, dbo.WHEEL_IMAGES.Wheel_Thumbnail AS Wheel_Thumbnail, dbo.WHEEL_IMAGES.Wheel_Image AS Wheel_Image, dbo.WHEELS.*, dbo.VENDOR_IMAGES.Vendor_Thumbnail AS Expr1, dbo.VENDOR_IMAGES.Vendor_AltTags AS Expr2 FROM WHEEL_CHARACTERISTICS INNER JOIN dbo.VENDORS ON WHEEL_CHARACTERISTICS.Vendor_ID = dbo.VENDORS.Vendor_ID INNER JOIN dbo.WHEEL_IMAGES ON WHEEL_CHARACTERISTICS.Wheel_ID = dbo.WHEEL_IMAGES.Wheel_ID INNER JOIN FILTER_CLIENT_WHEELS5 ON WHEEL_CHARACTERISTICS.Wheel_ID = FILTER_CLIENT_WHEELS5.Wheel_ID INNER JOIN dbo.WHEELS ON WHEEL_CHARACTERISTICS.Wheel_ID = dbo.WHEELS.Wheel_ID INNER JOIN CLIENT_WHEEL_PRICES5 ON FILTER_CLIENT_WHEELS5.Client_ID = CLIENT_WHEEL_PRICES5.ClientId AND WHEEL_CHARACTERISTICS.Wheel_Char_ID = CLIENT_WHEEL_PRICES5.Wheel_Char_ID INNER JOIN dbo.VENDOR_IMAGES ON dbo.VENDORS.Vendor_ID = dbo.VENDOR_IMAGES.Vendor_ID WHERE (dbo.VENDORS.Vendor_Active = 'y') AND (FILTER_CLIENT_WHEELS5.FCW_Active = 'y') AND (FILTER_CLIENT_WHEELS5.Client_ID = '1039') AND (WHEEL_CHARACTERISTICS.Wheel_Diameter =@SearchDiameter) AND (WHEEL_CHARACTERISTICS.Wheel_Width =@Searchwidth) AND (WHEEL_CHARACTERISTICS.Wheel_Bolt_Pattern_1 = @boltpattern1) if @myColours <> 'SelectAll' and WHEEL_CHARACTERISTICS.Wheel_Search_Colour = @myColours end if AND (cast(WHEEL_CHARACTERISTICS.wheel_Offset as int(4)) BETWEEN @minOffset AND @maxOffset) ORDER BY CLIENT_WHEEL_PRICES5.Price asc COMMIT TRANSACTION GO Anyone know how i should word the if...statements? I have not found anything that works yet. Thanks
View Replies !
Can't Use Stored Procedure In Query Where Clause???
Hi, By reading answers on the web I have found out that I can't use a stored procedure in a where clause of my query, but I can use a User defined function. This almost fits my needs but not quite. The function would work great if it could insert the results of its query into our cache table but you can't insert stuff into external tables to the function. The problem is that our stored procedure/function does looping to find parent objects way back up the tree to find out permissions for certain records. Since the stored procedure and function do so much querying to find the root most object that has permissions set there is a lot of reads in our call. We would like to cache this process so that next time they look for permissions it only does one read first. But in order for our caching to work the function needs to insert the results it found in our cache table which it can't do and the stored procedure can't be used in a where clause so that doesn't work. Any suggestions? Query looks like this, the query is built on the fly through code. select Title, Descriptions FROM defects df WHERE dbo.fnHasProjectRights(df.ProjectID); and that function first checks the cache table to see if it has ran before for that projectID and if not then starts doing all its logic to get permissions. Any suggestions how to approach this? I just wish functions could insert and or stored procedures could be used in the where clause since they can insert. Thanks,
View Replies !
Passing Datetime Variable To Stored Proc As Parameter
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 Replies !
Highly Dynamic Where Clause In A Stored Procedure
I have a situation where I'll need to get results from tables based on totally arbitrary filters. The user can select the field to compare against, the value, the comparison operator, and the boolean operator, so each bit in brackets would be configurable:[field] [>] [value] [and]The user can specify an arbitrary number of these, including zero of them. I like the coalesce function for situations that are a little more structured, but I think I'm stuck generating a dynamic query for this -- please correct e if I'm wrong!
View Replies !
How To Pass Values For The In Clause To The Stored Procedure?
hi friends,i need to select some of the employees from the EmpMaster using in clause. I tried to pass a string with the comma delemeters. it didn't produce all the records except the first in that string.shall i try with string functions in TSQL or any other options? Thanks and Regads,Senthilselvan.D
View Replies !
Stored Procedure With Optional/dynamic Where Clause
If I do this with a function and multiple inline sql statements, I could probably do it much easier, but here at work, sprocs are required, and I can't seem to stretch my knowledge and Google searches far enough to find the answer. Plus, I don't really think that creating 4 separate sProcs is the most efficient way of doing this I need to select and return 8 columns from a table, but the problem is I need to feed the sProc parameters in such a way, that I can use different criteria in the Where Clause. for instance, I need to combine these 4 select statements into one:1. Select (fields) from (table) Where TechID=@TechID and Status=@Status)2. Select (fields) from (table) Where TechID=@TechID3. Select (fields) from (table) Where OrdNum=@OrdNum3. Select (fields) from (table) Where CustNum=@CustNum In all instances, the fields and the table are the same - how can I combine all these possible Where clauses (if/then - Select Case?) so that it's only one Stored Procedure? (or, is this even possible?)
View Replies !
Stored Procedure Where Clause Not Working Properly
I am having a problem with this stored procedure. I'm using SQL Server 2005 Developer's edition and if I execute the procedure in a query window, I get no errors. Also, when the script runs from a website call there are no errors. The problem is that it doesn't return the information that is in the database. It is supposed to return the orders from Washington state between such and such dates. The orders are there in the database, so I think the where clause must be wrong. Thanks for the help. GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CommerceLibOrdersGetWashingtonState]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE [dbo].[CommerceLibOrdersGetWashingtonState] (@ShippingStateProvince VARCHAR(50), @ShippingCountry VARCHAR(50), @StartDate smalldatetime, @EndDate smalldatetime) AS SELECT OrderID, DateCreated, DateShipped, Comments, Status, CustomerID, AuthCode, Reference, ShippingCounty, ShippingStateProvince, ShippingCountry, ShippingID, TaxID, ShippingAmount, TaxAmount FROM Orders WHERE (DateCreated BETWEEN @StartDate AND @EndDate) AND (ShippingStateProvince = @ShippingStateProvince) AND (ShippingCountry = @ShippingCountry) ORDER BY DateCreated DESC' END
View Replies !
Building Where Clause Dynamically In Stored Procedure
Hello All, I have created SP in SQL 2K5 and make the where clause as parameter in the Sp. i am passing the where clause from my UI(ie ASP.NET), when i pass the where clause to SP i am not able to fetch the results as per the given criteria. WhereClause from UI: whereClause="where DefectImpact='High'" SQL Query in SP: SELECT @sql='select * from tablename' Exec(@sql + @whereClause ) Here i am not able to get the results based on the search criteria. Instead i am getting all the results. Please help me in this regard. Thanks, Subba Rao.
View Replies !
Using Table Name Stored In A Scalar Variable In Stored Procedure Problem
Hello to all! I have a table name stored in a scalar variable (input parameter of my stored procedure). I need to run SQL statement: SELECT COUNT (*) FROM MyTable and store the result of my query in a scalar variable: For example: declare @countRows int set @countRows = (select count(*) from MyTable) The problem is that the name of MyTable is stored in the input variable of my stored procedure and of corse this does not work: declare @countRows int set @countRows = (select count(*) from @myTableName) I also tried this: declare @sqlQuery varchar(100) set @sqlQuery = 'select count(*) from ' + @myTableName set @countRows = exec(@sqlQuery) But it looks like function exec() does not return any value... Any idea how to solve this problem? Thanx, Ziga
View Replies !
Procedure Or Function 'stored Procedure Name' Expects Parameter Which Was Not Supplied
Has anyone encountered this before? Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied. It seems that my code is not passing the parameter to the stored procedure. When I click this hyperlink: <asp:HyperLink ID="HyperLink1" Runat="server" NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>' Text='<%# Eval("Name") %>' ToolTip='<%# Eval("Description") %>' CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'> </asp:HyperLink> it is suppose to get the country name and description, based on the country id. I am passing the country id like this. protected void Page_Load(object sender, EventArgs e) { PopulateControls(); } private void PopulateControls() { string countryId = Request.QueryString["CountryID"]; if (countryId != null) { CountryDetails cd = DivisionAccess.GetCountryDetails(countryId); divisionNameLabel.Text = cd.Name; divisionDescriptionLabel.Text = cd.Description; } } To my app code like this: public struct CountryDetails { public string Name; public string Description; } public static class DivisionAccess { static DivisionAccess() public static DataTable GetCountry() { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "GetCountry"; return GenericDataAccess.ExecuteSelectCommand(comm); } public static CountryDetails GetCountryDetails(string cId) { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "GetCountryDetails"; DbParameter param = comm.CreateParameter(); param.ParameterName = "@CountryID"; param.Value = 2; param.DbType = DbType.Int32; comm.Parameters.Add(param); DataTable table = GenericDataAccess.ExecuteSelectCommand(comm); CountryDetails details = new CountryDetails(); if (table.Rows.Count > 0) { details.Name = table.Rows[0]["Name"].ToString(); details.Description = table.Rows[0]["Description"].ToString(); } return details; } As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied Someone please help! Thanks Nickdel68
View Replies !
Passing An Array Into A Stored Procedure And The 'IN' Clause Will Not Do The Trick
I have a table that looks like this: RecordId PictureId KeywordId 111 212 313 421 522 623 725 817 932 1044 I need to run a query where I pass in an unknown number of KeywordIds that returns the PictureId. The 'IN' clause will not work because if a KeyWordId gets passed into the Stored Procudure the PictureId must have a record with each KeyWordId being passed in. For example, lets say you need to see the result of all PictureIds that have both 1 and 2, the correct result set should only be PictureId 1 and PictureId 2. Im going crazy trying to find a simple solution for this. Please advise.
View Replies !
Syntax Error When Building Up A Where Clause In Stored Procedure
Can anyone tell me why the line highlighted in blue produces the following error when I try to run this stored proc? I know the parameters are set properly as I can see them when debugging the SP. I'm using this type of approach as my application is using the objectdatasource with paging. I have a similar SP that doesn't have the CategoryId and PersonTypeId parameters and that works fine so it is the addition of these new params that has messed up the building of the WHERE clause The Error is: "Syntax error converting the varchar value ' WHERE CategoryId = ' to a column of data type int." Thanks Neil CREATE PROCEDURE dbo.GetPersonsByCategoryAndTypeByName (@CategoryId int, @PersonTypeId int, @FirstName varchar(50)=NULL, @FamilyName varchar(50)=NULL, @StartRow int, @PageSize int) AS Declare @WhereClause varchar(2000)Declare @OrderByClause varchar(255)Declare @SelectClause varchar(2000) CREATE TABLE #tblPersons ( ID int IDENTITY PRIMARY KEY , PersonId int , TitleId int NULL , FirstName varchar (50) NULL , FamilyName varchar (50) NOT NULL , FullName varchar (120) NOT NULL , AltFamilyName varchar (50) NULL , Sex varchar (6) NULL , DateOfBirth datetime NULL , Age int NULL , DateOfDeath datetime NULL , CauseOfDeathId int NULL , Height int NULL , Weight int NULL , ABO varchar (3) NULL , RhD varchar (8) NULL , Comments varchar (2000) NULL , LocalIdNo varchar (20) NULL , NHSNo varchar (10) NULL , CHINo varchar (10) NULL , HospitalId int NULL , HospitalNo varchar (20) NULL , AltHospitalId int NULL , AltHospitalNo varchar (20) NULL , EthnicGroupId int NULL , CitizenshipId int NULL , NHSEntitlement bit NULL , HomePhoneNo varchar (12) NULL , WorkPhoneNo varchar (12) NULL , MobilePhoneNo varchar (12) NULL , CreatedBy varchar(40) NULL , DateCreated smalldatetime NULL , UpdatedBy varchar(40) NULL , DateLastUpdated smalldatetime NULL, UpdateId int ) SELECT @OrderByClause = ' ORDER BY FamilyName, FirstName' SELECT @WhereClause = ' WHERE CategoryId = ' + @CategoryId + ' AND PersonTypeId = ' + @PersonTypeIdIf NOT @Firstname IS NULLBEGIN SELECT @WhereClause = @WhereClause + ' AND FirstName LIKE ISNULL(''%'+ @FirstName + '%'','''')'ENDIf NOT @FamilyName IS NULLBEGIN SELECT @WhereClause = @WhereClause + ' AND (FamilyName LIKE ISNULL(''%'+ @FamilyName + '%'','''') OR AltFamilyName LIKE ISNULL(''%'+ @FamilyName + '%'',''''))'END Select @SelectClause = 'INSERT INTO #tblPersons( PersonId, TitleId, FirstName, FamilyName , FullName, AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeathId, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, HospitalId, HospitalNo, AltHospitalId, AltHospitalNo, EthnicGroupId, CitizenshipId, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated, UpdateId) SELECT PersonId, TitleId, FirstName, FamilyName , FullName, AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeathId, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, HospitalId, HospitalNo, AltHospitalId, AltHospitalNo, EthnicGroupId, CitizenshipId, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated, UpdateId FROM vw_GetPersonsByCategoryAndType ' EXEC (@SelectClause + @WhereClause +@OrderByClause)
View Replies !
How To Pass Value For The “in� Clause In The SQL Server Stored Procedure?
How to pass value for the “in� clause in the SQL server stored procedure? Let us consider the following code. declare @Country varchar(200) set @CountryIds='1,11' select CountryName from COUNTRY where CountryId in (@CountryIds) GO In the above code, the lists of country ids are needed to feed from the front end. In the above example, we need to select countries for 1 and 11. So, how to pass this from front end and what will be the data type of the parameter. I am using front end as .Net. Please help in this regard. The stored procedure what I wrote was. CREATE PROCEDURE [cmp].[Events_By_Country] @CountryIds varchar(200) AS select CountryName from COUNTRY where CountryId in (@CountryIds) GO Which is not working.
View Replies !
Dynamic Built Where Clause - Error In Stored Procedure
I'm sending a stored procedure a number of parameters, including an int for a foreign key field. I'm trying to dynamically build the Where Clause of the query. I'm getting the error: Syntax error converting the varchar value 'wo_dutfk = ' to a column of data type int. NOTE: wo_dutfk is an int field within the WO table. Here's the code: CREATE PROCEDURE dbo.ewo_sp_WOLookup ( @WODUTFK int=NULL, @WOENDDATE datetime = NULL, @WOSTATUS char(10) = NULL, @DEBUG int=0 ) AS Declare @intErrorCode int, @chvQuery varchar (8000), @chvWhere varchar (8000) select @intErrorCode = @@ERROR, @chvQuery='SET QUOTED_IDENTIFIER OFF select * from WO', @chvWhere='' -- DUT is specified If @intErrorCode = 0 and @WODUTFK is not NULL Begin set @chvWhere = @chvWhere + 'wo_dutfk = '+@WODUTFK End IF @intErrorCode=0 and Len(@chvWhere)>0 Begin set @chvQuery = @chvQuery + ' WHERE ' + @chvWhere select @intErrorCode=@@ERROR END IF @DEBUG<>0 select @chvQuery Query IF @intErrorCode=0 Begin exec (@chvQuery) select @intErrorCode=@@ERROR End return @intErrorCode GO --- Any suggestions on how to fix? Thanks, peter
View Replies !
WHERE Clause' Search Condition From Argument In A Stored Procedure.
Hello I wonder if someone could suggest a way to obtain the following. Using SQL Server 2005 I want to create some stored procedures. I want to query the DB with various filter arguments and combinations of these. One way would be to create one stored procedure for each function signature. However, as the number of combinations of filter is large, if possible I'd rather have a generic input to the each stored procedure that corresponds to the entire WHERE clause' search condition. The stereotype behavior I'm looking for is: SELECT myField FROM myTable WHERE @mySearchCondition Does any one have some good suggestion, code samples and/or links? Kind regards Jens Ivar
View Replies !
Problem Creating A Stored Procedure With The Execute As Clause.
I have a server with a login named NewCreator. This login is assigned only to the dbcreator server role. I want this login to be able to execute the stored procedure, sp_helplogins. The sp_helplogins documentation says the caller of this proecedure needs securityadmin permissions. I know that I should be able to create a login, say 'SecAdmin' and assign that login to the security admin server role, and then grant NewCreator Impersonate permission on SecAdmin. What I don't like about this technique is that NewCreator can get on the server and gain complete authority over security when I really only want him to be able to execute sp_helplogins. Therefore, what I am attempting is the following: I create the login, SecAdmin, and assign it to the securityadmin server role. Then I right click sp_helplogins and select 'Modify'. I change the Alter to Create, change the name of the procedure to sp_myspecialone, and then add the sql statement "With Execute as 'SecAdmin'. I execute the above and the new stored procedure is created. I then grant Execute on sp_myspecialone to NewCreator. Everything works fine so far. But when I login as NewCreator and execute sp_myspecialone, I get an error in line 73 - no permission. Line 73 is blank, but the next few lines read: if (not (is_srvrolemember('securityadmin') = 1)) begin raiserror(15247,-1,-1) select @RetCode = 1 goto label_86return end What am I doing wrong? Is their a better alternative to accomplish what I want to do? Your assistance would be greatly appreciated.
View Replies !
RS 2005: Stored Procedure With Parameter It Runs In The &&"Data&&" Tab But The Report Parameter Is Not Passed To It
Hello, since a couple of days I'm fighting with RS 2005 and the Stored Procedure. I have to display the result of a parameterized query and I created a SP that based in the parameter does something: SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE PROCEDURE [schema].[spCreateReportTest] @Name nvarchar(20)= '' AS BEGIN declare @slqSelectQuery nvarchar(MAX); SET NOCOUNT ON set @slqSelectQuery = N'SELECT field1,field2,field3 from table' if (@Name <> '') begin set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + '''' end EXEC sp_executesql @slqSelectQuery end Inside my business Intelligence Project I created: -the shared data source with the connection String - a data set : CommandType = Stored Procedure Query String = schema.spCreateReportTest When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed. Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter... So inside"Layout" tab, I added the parameter: Name allow blank value is checked and is non-queried the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!! What is wrong? What I forgot?? Thankx for any help! Marina B.
View Replies !
Set Variable With Stored Procedure Or...
hi how can I set my variable value with a query??? I am about to make a procedure to get a comma seperatet array and insert into the table. if I omit ID (primary key) and set it as Identity every thing is ok but I don't want it to be Identity. so I have to find the max of ID and then add it to the first of my comma seperated values. the problem is I have a table name as nvarchar in the parameters. so I have to first make a query as string and then execute it. anyway I need to get the value of the query string and assingn @ID variable to it's return value. how can I do it? so just suppose that I have @Values, @TableName (values are in the right order) then I want to find max(ID)+1 in @TableName (something like "select max(id)+1 from @TableName" I know that it will not work. so I make a string and execute it ) and add it to the first of my @Values then my problem will solve thank you in advance regards
View Replies !
Using Variable In Stored Procedure - Help!
HelloI am a newbie to this, so I would appreciate any help, I am strugglingto get this to workCREATE PROCEDURE [dbo].[sp_test]@strfinalint as varchar(1000),@startdate as datetime@enddate as datetimeasdeclare @insertcmd as varchar(2000)declare @startdate as datetimedeclare @enddate as datetimeset @insertcmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=d:MyFolder' + @strfinalint + ';'',''SELECT * FROM [Sheet1$]'') Select * from tbltest WHERE S_DateBetween' + @startdate + 'AND' + @enddateEXEC (@insertcmd)GOIt was working with the commandset @insertcmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=d:MyFolder' + @strfinalint + ';'',''SELECT * FROM [Sheet1$]'') Select * from tbltest'But I am struggling to include the WHERE part of it, I seem to haveproblems making the variables work in this with appropriate quotationmarks !!ThanksSunny
View Replies !
Stored Procedure Into Variable?
Hello experts! I have a problem. I am trying to get a value from stored procedure A into a variable in storded procedure B, kind of like this: Note: don't be fooled by the simplicity of these samples. There is no other way for me to solve this problem, but to get the value from sp_A into a variable in sp_B, since sp_A is in a database out of my control and i have no clue what happens therein... CREATE PROCEDURE sp_A AS SELECT 'Hello world!' RETURN GO CREATE PROCEDURE sp_B AS DECLARE @Value nvarchar(50) SET @Value = --** Here i want to get the value from sp_A SELECT @Value GO Thanks! /Murti
View Replies !
Using Top @variable In A Stored Procedure?
I am building a stored procedure to get a list of rows, and I want to limit the rows returned to a variable. However, i keep getting a syntax error. SQL: SELECT TOP @TopArticles ArticleTable.*, ArticleSubSectionTable.SubSectionID AS SSID, ArticleTable.ArticleDate AS EXPR1 FROM ArticleTable ERROR Error 170: Line 28: Incorrect Syntax near '@TopArticles' Any help would be appreciated, PK
View Replies !
Set Variable With Stored Procedure Or...
hi how can I set my variable value with a query??? I am about to make a procedure to get a comma seperatet array and insert into the table. if I omit ID (primary key) and set it as Identity every thing is ok but I don't want it to be Identity. so I have to find the max of ID and then add it to the first of my comma seperated values. the problem is I have a table name as nvarchar in the parameters. so I have to first make a query as string and then execute it. anyway I need to get the value of the query string and assingn @ID variable to it's return value. how can I do it? so just suppose that I have @Values, @TableName (values are in the right order) then I want to find max(ID)+1 in @TableName (something like "select max(id)+1 from @TableName" I know that it will not work. so I make a string and execute it ) and add it to the first of my @Values then my problem will solve thank you in advance regards
View Replies !
Variable In Stored Procedure
Hi everybody, i have a problem and i hope anyone can help me. I write a sp thayt include some variables. One of it is nvarchar(max) (because i think needed value is too big). Unfortunately, that variable is really too big (about 1000000 chars). It is out of bound of nvarchar(max). How can i do?? Please help me because i am working in it and it is too late now. Thanks.
View Replies !
Stored Procedure Return Value And A Variable
hi everybody, I have two stored procedures lets say A and B and a variable inside stored procedure A lets say @BReturn, now, what is the syntax to execute B inside A and to store its returned value in @BReturn? any help is greatly appriciated. devmetz.
View Replies !
Stored Procedure With Order By Variable
I am trying to create a stored procedure that is sent a column name and orders by the column name. conn = New SqlConnection(SQLserver)SQL = New SqlCommand("SearchECN", conn) SQL.CommandType = CommandType.StoredProcedure SQL.Parameters.Add("@Search", SqlDbType.Variant)SQL.Parameters.Add("@Sort", SqlDbType.Variant) SQL.Parameters(0).Value = Search.Text SQL.Parameters(1).Value = "ECN.ECN" SQL.Connection.Open() GVECN.DataSource = SQL.ExecuteReader() GVECN.DataBind() SQL.Connection.Close() SQL.Connection.Dispose() Stored Procedure @Search NVARCHAR(MAX), @Sort NVARCHAR(MAX) SELECT ECN.ECN, ECN.A, ECN.B FROM ECN WHERE ECN.ECN LIKE @Search OR ECN.A LIKE @Search ORDER BY @Sort I get the following error Msg 1008, Level 16, State 1, Line 10 The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name. Any Ideas
View Replies !
Adding A Variable To A Stored Procedure
Hi,I just try to add a variable (ORDER BY) to the end of a select statement and it doesn't work. I try so many things but im a newbie in SQL.Help!!!I want to add @OrderBy at the end of :-----------------SELECT *FROM AnnoncesWHERE CategoryID = @CategoryID AND DateOnCreate >= @DateFinale-----------------My declaration :Declare @OrderBy nvarchar(50)If @Sort = 'date_de' set @OrderBy = ' ORDER BY DateOnCreate DESC'Else set @OrderBy = ' ORDER BY DateOnCreate ASC'
View Replies !
How To Pass A Variable To The Stored Procedure?
Hi, i need to insert a record 1 or more times, depending of a variable in code-behind:dim amount as integeramount= value (e.g. 3) My problem is: how to pass that variable to the stored procedure?I tried with this but nothing happens: comd.Parameters.Add("@amount", SqlDbType.NVarChar, 10).Value = amount_of_details Maybe is my stored procedure wrong? Thanks T. Here is it:---------- ALTER PROCEDURE dbo.insert_table (@field1 nvarchar(10),...)ASDeclare @iLoopNumber intDeclare @amount intBEGIN TRAN SET @iLoopNumber = 1 SET @amountr While (@iLoopNumber <= @amount) BEGIN INSERT INTO table(field1,...) VALUES (....)) SET @iLoopNumber = @iLoopNumber +1 End COMMIT TRAN
View Replies !
Variable Sorting In Stored Procedure
Hi I am trying to achieve something like: ALTER PROCEDURE dbo.GetShares @SortValue varChar(30)= SHARE_DESCRIPTION as SELECT SHARES.SHARE_DESCRIPTION, SHARES.SHARE_SYMBOL, SECTORS.SECTOR_NAME FROM SHARES INNER JOIN SECTORS ON SHARES.SECTOR_ID = SECTORS.SECTOR_ID ORDER BY @SortValue but it does not seem to be possible to use variable after order by is there any way to achieve something with sorting by variable?
View Replies !
What Wrong With This Variable In Stored Procedure?
Hi all. 1. I have a stored procedure, the procedure need create new category for forums, each category have their own place (what going first , what going second etc...), it's importent for me that all categories will be in property for them place, so i must read next place from table, and if not then i set it = 0 . But it's seems not working, i getting error. Can somebody tell me please what wrong ? ALTER PROCEDURE dbo.CreateCategory ( @category_name varchar(100) ) AS declare @place int /* setting place to zero */ SET @place = 0 /* trying to get last place and set value into @place */ SET @place = (Select max(category_place) FROM fo_categories) /* if got nothing from table then setting value of first category */ IF (@place = 0) set @place = 1 INSERT fo_categories (category_name, category_place) VALUES(@category_name, @place) RETURN Running dbo."CreateCategory" ( @category_name = Public forums ). Cannot insert the value NULL into column 'category_place', table 'mg_forum.dbo.fo_categories'; column does not allow nulls. INSERT fails. 2. I also would ask what SET NOCOUNT ON/OFF mean and for what i can use it ?
View Replies !
|
TRACKER
