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 Complete Forum Thread with Replies
Related Forum Messages:
Define Stored Procedure Columns
The SQL below is the start of a massive Stored Procedure for Comparing two Datasets, which will be produced onto a report. I was wondering if I could call an SQLserver Procedure that would tell me the names of all the Columns that are produced by this SP, so I can print them out and more easily code the report? SELECT stk.StockNumber, stk.DefaultImageName, tVTP.Make as PolMake, stkV.VehicleMake, tVTP.Model as PolModel, stkV.VehicleModel, tVTP.ModelNo as PolModelNo, stkV.VehicleModelNo, tVTP.EngineNumber as PolEngineNumber, Stk.EngineNumber, tVTP.comHeadLightNumber as PolHeadLightNumber, Stk.comHeadLightNumber, tVTP.comTailLightNumber as PolTailLightNumber, Stk.comTailLightNumber , tVTP.comBumperLightNumber as PolBumperLightNumber, Stk.comBumperLightNumber, tVTP.comCornerLightNumber as PolCornerLightNumber, Stk.comCornerLightNumber, tVTP.Chassis as PolChassis, --Drive Train tVD.DriveTrainDescription as POlDriveTrainDescription, StktVD.DriveTrainDescription, --Body Type tVBT.BodyTypeDescription as PolBodyDescription , StkVBT.BodyTypeDescription FROM tblStock Stk --JOINS FOR THE Policy Definition INNER JOIN tblVehicles V ON V.VehicleID = Stk.VehicleID INNER JOIN tblVehicleType_Policy tVtP ON tVTP.VehicleMaster = V.VehicleMaster AND tVTP.Make = V.VehicleMake AND tVTP.Model = V.VehicleModel AND tVTP.ModelNo = V.VehicleModelNo INNER JOIN tblVehicleDriveTrain tVD ON tVD.vehicleDrivetrainID = tVTP.DrivetrainID INNER JOIN tblvehicleBodyType tVBT ON tVBT.VehicleBodyTypeID = tVTP.BodyTypeID --JOINS FOR the Stock Definition INNER JOIN tblVehicles STkV ON StkV.VehicleID = Stk.VehicleID INNER JOIN tblvehicleBodyType StkVBT ON StkVBT.VehicleBodyTypeID = Stk.BodyTypeID INNER JOIN tblVehicleDriveTrain StktVD ON StktVD.vehicleDrivetrainID = stk.DrivetrainID --INNER JOIN tbl WHERE Stk.StockNumber LIKE 'V%'
View Replies !
Is There A Sample Way To Define String Constant Which Every Stored Procedure Can Use In SQL 2005 ?
Is there a sample way to define string constant which every stored procedure can use in SQL 2005 ? 1. In stored procedure A, there is select a1,a2,a3,a4 from mytable where usename='qaz'2. In stored procedure B, there isselect a1,a2,a3,a4 from mytable where VisitNumber>33. I hope there is a sample way to define string constant such as: constant mystring='a1,a2,a3,a4'4. So I can use this string constant both stored procedure A and stored procedure bsuch as:select mystring from mytable where usename='qaz' select mystring from mytable where VisitNumber>35. How can I do that? is there a sample way? Mnay Thanks!
View Replies !
Accessing A Stored Procedure From ADO.NET 2.0-VB 2005 Express:How To Define/add 1 Output && 2 Input Parameters In Param. Coll.?
Hi all, In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3": CREATE PROC [dbo].[spInvTotal3] @InvTotal money OUTPUT, @DateVar smalldatetime = NULL, @VendorVar varchar(40) = '%' This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in my SSMSE by using either of 2 sets of the following EXEC code: (1) USE AP GO --Code that passes the parameters by position DECLARE @MyInvTotal money EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%' PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1) GO (2) USE AP GO DECLARE @InvTotal as money EXEC spInvTotal3 @InvTotal = @InvTotal OUTPUT, @DateVar = '2006-06-01', @VendorVar = '%' SELECT @InvTotal GO //////////////////////////////////////////////////////////////////////////////////////////// Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code: Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Public Class Form1 Public Sub printMyInvTotal() Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;" Dim conn As SqlConnection = New SqlConnection(connectionString) Try conn.Open() Dim cmd As New SqlCommand cmd.Connection = conn cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "[dbo].[spInvTotal3]" Dim param As New SqlParameter("@InvTotal", SqlDbType.Money) param.Direction = ParameterDirection.Output cmd.Parameters.Add(param) cmd.ExecuteNonQuery() 'Print out the InvTotal in TextBox1 TextBox1.Text = param.Value Catch ex As Exception MessageBox.Show(ex.Message) Throw Finally conn.Close() End Try End Sub End Class ///////////////////////////////////////////////////////////////////// I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!?? I have 4 questions to ask for solving the problems in this project: #1 Question: I do not know how to do the "DataBinding" for "Name" in the "Text.Box1". How can I do it? #2 Question: Did I set the CommandType property of the command object to CommandType.StoredProcedure correctly? #3 Question: How can I define the 1 output parameter (@InvTotal) and 2 input parameters (@DateVar and @VendorVar), add them to the Parameters Collection of the command object, and set their values before I execute the command? #4 Question: If I miss anything in print out the result for this project, what do I miss? Please help and advise. Thanks in advance, Scott Chang
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 !
How To Define A Variable In Expression In SSRS
Hello Friends, I am new to SSRS and i want to define a Variable to one of the expression of my ssrs report. Can anyone help me to solve this issue. I am using a matrix in the report and in that matrix in each box i am using some expression to get the value. So i need to define a variable .. Is it possible to define a variable ?
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 !
How To Define A User Variable On Execute Sql Task??
Hi everyone, How to define a Input variable in a Execute Sql Task?? I've defined a User::Inicio variable which contains 4 as value. In Parameter Mappins it has been defined. Then, I've gone to General->Sql Statement and allocated the following SQL Statement: UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [Inicio] or UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [User::Inicio] Anyway, I'm stuck, both did not work Thanks in advance for your comments
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
Variable Always Empty In Stored Procedure
In the code below, the statement 'Print @Sites' prints nothing, eventhough the cursor contains 4 records, and 'Print @Site' prints theappropriate values. Can anyone see my mistake? I am attempting toprint a delimited string of the values referred to by @Sites.Thanks.Dan FishermanDECLARE SiteCursor CURSORGLOBALSCROLLSTATICFOR SELECT OfficeName FROM ClientOffices WHERE ClientID=12 ORDER BYOfficeNameOPEN SiteCursorDECLARE @Sites varchar(1000)DECLARE @Site varchar(100)FETCH NEXT FROM SiteCursor INTO @SiteWHILE @@FETCH_STATUS=0BEGINprint @SiteSET @Sites = @Sites + ', ' + @SiteFETCH NEXT FROM SiteCursor INTO @SiteENDPRINT @SitesCLOSE SiteCursorDEALLOCATE SiteCursorGO
View Replies !
Stored Procedure With Output Variable
Can someone post a working stored procedure with output variable thatworks on northwind db or explain to me what I am missing. I've triedever tying but it always returns @outvariable parameter not supplied.I've read everything but must be missing something. Here is an sampleSP that I can't get to work._____________________________________CREATE PROCEDURE SampleProcedure@out smallint OUTPUTASSelect @out = count(*) from titlesGO_____________________________________This errors and returns-Server: Msg 201, Level 16, State 4, Procedure SampleProcedure, Line 0Procedure 'SampleProcedure' expects parameter '@out', which was notsupplied.What am I missing with this?Frustrated,Jeff
View Replies !
Variable Table Name In A Stored Procedure
Hello Forum !I want to have the tablename "dbo.Enbxxxx" as an additional parameterfor a procedure like this:ALTER Procedure prcSucheUNR(@UNR int)Asset nocount onSELECT ABRUFNR,UNR,STICHTAG,Datum,InhaltINTO #temp FROM dbo.Enb WHERE UNR = @UNRFor some reason:@tablename varchar(11),and: INTO #temp FROM @tablename WHERE .... does not work. :-(I get the following syntax Error:Zeile 33: Falsche Syntax in der Nähe von '@tablename'.I'am sure it is possible but i don't know how.Greetings
View Replies !
|
TRACKER
