I have 3 tables: Authors, Employee and Stores.
I need to create a stored procedure which will take 3 comma delimited parameters, to be able to query above 3 tables.
Basically my front end user can say give me Authors with last name starting from ‘A,B’ and Employee with first name starting from ‘J,N,K’ and Stores with city starting from ‘New, Los’.
So, stored procedure call will look like this Exec myStoredProcedure 'A,B' , 'J,N,K', 'New,Los' .
My question is, how should I handle WHERE clause in stored procedure if I don’t want it to be a “dynamic WHERE�.
What do you do if you need to select item 20 to 40 from a table? Do you just do 1 to 40 and let PHP ignore the first 20, or do you have another equivalent of the MySQL LIMIT Clause for MSSQL?
I have a procedure that take several paramters and depending of whatvalues is submitted or not, the procedures shall return differentnumber of rows. But to simplyfy this my example use just oneparameter, for example Idnr.If this id is submitted then I will return only the posts with thisidnr, but if this is not submitted, I will return all posts in table.As I can see I have two options1. IF @lcIdNr IS NOT NULLSELECT *FROM tableWHERE idnr = @lcIdNrELSESELECT *FROM table2. Use dynamic SQL.The first example can work with just one parameter but with a coupleof different input paramters this could be difficult, anyway this isnot a good solution. The second example works fine but as I understanddynamic sql is not good from the optimizing point of view. So, I don'twant to use either of theese options, so I wonder If there i a way towork around this with for example a case clause?RegardsJenny
Hi all, I have a sql problem i'd like to put to the masses because its driving me crazy! Before I start, this is a database i inherited so I cant change the schema. I have a table which holds field information for a form, namely the table name, column name and some other irrelevant stuff (X/Y coordinates for printing onto a document). Here is some sample data to explain better: TableName FieldName Xpos Ypos ---------- --------- ----- ----- FruitTable FruitName 10 20 VegTable VegName 10 40 FruitTable FruitColour 20 10 (Thats not the real data of course) What I need is a calculated field which returns the value of each field from each table – probably by constructing a dynamic sql statement(?) It would look something like this: Select @FieldName From @TableName Where bla bla bla – don’t worry about the where clause. The completed dataset will hopefully then look like this: TableName FieldName Xpos Ypos FieldValue (calculated field) ---------- --------- ----- ----- --------- FruitTable FruitName 10 20 Oranges (result of: Select FruitName From FruitTable Where....) VegTable VegName 10 40 Parsnips (result of: Select VegName From VegTable Where....) FruitTable FruitColour 20 10 Green (result of: Select FruitColour From FruitTable Where....)
I have tried creating a scalar-valued function which takes TableName and FieldName as parameters and creates a dynamic sql string, but i cannot seem to execute the sql once I have built it. Here is a general idea of how I was trying to use the function: Main query:Select TableName, FieldName, Xpos, Ypos, dbo.GetFieldValue(TableName, FieldName) As FieldValue From tblFieldAndPosition---------------Function: CREATE FUNCTION GetFieldValue (@TableName nvarchar(255),@FieldName nvarchar(255))
RETURNS nvarchar(255) AS BEGIN
Declare @SQL nvarchar(max) Set @SQL = 'Select ' + @FieldName + ' From ' + @TableName
sp_executesql @SQL??
return ???
END ------------------------- The alternative to getting this data all out at once is contructing the sql statement in code and going back to the database once for every row - which i really dont want to do. If anyone has had a situation like this before, or can point me in the right direction I will be very very grateful. Hope thats clear. Thanks in advance
Hi all, I have a sql problem i'd like to put to the masses because its driving me crazy! Before I start, this is a database i inherited so I cant change the schema.
I have a table which holds field information for a form, namely the table name, column name and some other irrelevant stuff (X/Y coordinates for printing onto a document). Here is some sample data to explain better:
What I need is a calculated field which returns the value of each field from each table €“ probably by constructing a dynamic sql statement(?) It would look something like this:
Select @FieldName From @TableName Where bla bla bla €“ don€™t worry about the where clause. The completed dataset will hopefully then look like this:
I have tried creating a scalar-valued function which takes TableName and FieldName as parameters and creates a dynamic sql string, but i cannot seem to execute the sql once I have built it. Here is a general idea of how I was trying to use the function: Main query:Select TableName, FieldName, Xpos, Ypos, dbo.GetFieldValue(TableName, FieldName) As FieldValue From tblFieldAndPosition---------------Function: CREATE FUNCTION GetFieldValue (@TableName nvarchar(255),@FieldName nvarchar(255))
RETURNS nvarchar(255) AS BEGIN
Declare @SQL nvarchar(max) Set @SQL = 'Select ' + @FieldName + ' From ' + @TableName
sp_executesql @SQL??
return ???
END ------------------------- The alternative to getting this data all out at once is contructing the sql statement in code and going back to the database once for every row - which i really dont want to do. If anyone has had a situation like this before, or can point me in the right direction I will be very very grateful. Hope thats clear. Thanks in advance
I am unfortunately lumered with a table I cannot edit that stores a division 2 characters along with boolean fields '0506', '0607', '0708' ... (academic years) to represent if that particular division is active in the current academic year. This has made me have to resort to dynamic sql within a stored procedure to input the appropriate field names.
Is there a better way to do it, it's not mission critical to make it not use dynamic sql but I would prefer not to.
I am looking to do a dynamic IN clause, such as the following.create procedured usp_SPTest
@variable varchar(150) AS
select * from table where ID IN (@variable) GO
Then execuuting this withusp_SPTest '''1'',''2''' Basically this won't work and gives an error Syntax error converting the varchar value ''1','2'' to a column of data type int. which makes sense. Is there anyway to do this outside of a dynamic SQL statement? According to http://www.sommarskog.se/dynamic_sql.html#List it doesn't look promising but unpacking the list into a table is a bit over board for this in my opinion. Any help is appreciated.
Hi. I can't seem to get my dynamic where clause right.
The pseudo code is such:
when gender is not null and is not 'show all' then create the condition "when tab1.gender = @gender". and, when country is not null and is not 'show all' then create the condition "when tab1.country = @country". and, finally, create the condition where tab1.bday_year is between @toage AND @fromage (this will never be empty or null).
I have tried many variations, the most recent being below. But, the resulting data does not act as it should (or it won't compile) :
Code:
WHERECASEWHEN @gender <> 'show all' THEN tab1.gender = @gender WHEN @country <> 'show all' THEN tab1.country = @country tab1.bday_year BETWEEN @toage AND @fromage END
HiI need some advice on which direction to take!Consider this statement:SELECT business_name FROM myTable WHERE town = @town AND county = @countyMy problem is that i will not always have the @county variable available. Is there a way to use an IF or a CASE inside the SQL statement (i know i can create two seperate sql statments but dont want to do it this way)? If it makes it easier, when the @county variable is not available, it has a value of 0.thanks againPs, i also know how to do it using dynamic sql using the EXEC() command, but i'd prefer to steer clear of this method also.
I pass a WHERE clause to a sp and use EXEC to execute it. it works fine if it does not include datetime Type in WHERE clause. however, if it did, the an error will be shown up "Unclosed quotation mark after the character string '2'.". I try to copy that clause and run as query in SQL server. there is no problem.
the Where clause is (Status='Open' or Status='On Going' or Status='Hold' or Status='Close') And (Found_Date BETWEEN ' '2007-11-01'' And ''2007-11-30'')
I am trying to write a stored procedure usp_select using dynamic sql to select from a table. The stored procedure will accept the where clause and/or the where clause parameters. I have tried 3 different methods -
Method 1 - exec usp_select @whereCondition='col1 like ''abc%'' and col2 = ''xyz''' In usp_select, I'll build and execute the sql like - set @sql = N'select * from table ' + @whereConition exec sp_executesql @sql (basically @sql becomes - select * from table where col1 like 'abc%' and col2 = 'xyz')
Method 2 - exec usp_select @whereCondition='col1 like @p1 and col2 = @p2', @WhereParams='@p1=abc%,@p2=xyz'
In usp_select, I'll parse out the values in @WhereParams and then build and execute the sql like -
(basically @sql becomes - declare @p1 nvarchar(10), @p2 nvarchar(10); set @pt = 'abc%', @p2 = 'xyz'; select col1 from table1 where col1 like @p1 and col2 = @p2)
Method 3 -
similar to Method 2 but exec(@sql) will be structured to become - exec(declare @vparam nvarchar(100), @p1 nvarchar(10), @p2 nvarchar(10);
set @vparam='@p1 nvarchar(10), @p2 nvarchar(10)'
set @p1 = 'abc%', @p2 = 'xyz';
execute sp_executesql N''select col1 from table1 where col1 like @p1 and col2 = @p2', @vparam, @p, @p2)
When I run sql profiler on the 3 methods, method 1 and 2 always result in a Cache Miss on the entire sql structure. On method 3, a Cache Miss always occurs on the first part of the sql, ie, the first 3 lines where I declare and set the variables. Then a Cache Hit will happen on the execute sp_executesql part.
Do I have any performance gain using method 3 with both a Cache Miss and a Cache Hit?
I hope this is not too confusing. Because I do not know the where condition to the select procedure and hardcoding the values as in method 1 always results in a Cache Miss, therefore, I come up with the ideas in Method 2 and 3.
I have a table (tblRestore) that has the following data FilePath Included FileType C:DataProjectsBeacon 1 Directory C:DataProjectsBeaconPDA 1 Directory C:DataProjectsBeaconBeaconVisual Studio 2005 0 Directory C:DataProjectsBeaconVisual Studio 2005 0 Directory C:DataProjectsBeaconPDAAuditPDAWM5 0 Directory
I want to use the data in this table to dynamically create a where clause that would look like this
WHERE (ParentDirectory + '' LIKE 'C:DataProjectsBeacon\%' OR ParentDirectory + '' LIKE 'C:DataProjectsBeaconPDA\%')
AND (ParentDirectory NOT LIKE('C:DataProjectsBeaconVisual Studio 2005%')
AND ParentDirectory NOT LIKE('C:DataProjectsBeaconBeaconVisual Studio 2005%')
AND ParentDirectory NOT LIKE('C:DataProjectsBeaconPDAAuditPDAWM5%')
In a stored procedure I am trying to use a parameter in a where clause which is set dependent upon another parameter... ie if report 1 is run, I want to evaluate against the StartDate field ... otherwise evaluate against the EndDate field.
Hi, i have a stored procedure like this: CREATE PROCEDURE dbo.spQuery1
@dynamicField nvarchar(10)
AS
SELECT id, productName, productDescription
FROM products
WHERE @dynamicField = 'True'
RETURN The products table has id, productName, productDescription, property1, property2, property3 fields So my query variable should be, for example, "property1" and the result should be every row with "property1" set to true... but it doesnt work with the sql like this... any help?
I have a stored procedure which expects one parameter @Company The variable @Company holds pipe delimited value: "CNN|AOL|ABC"
I need to build a WHERE clause by parsing @Company value, so the select will look like below:
SELECT * FROM Company WHERE CompanyID IN (SELECT DISTINCT(CompanyID) FROM v_Company WHERE CompanyName = 'CNN') AND CompanyID IN (SELECT DISTINCT(CompanyID) FROM v_Company WHERE CompanyName = 'AOL') AND CompanyID IN (SELECT DISTINCT(CompanyID) FROM v_Company WHERE CompanyName = 'ABC')
I have a project which is need dynamically t-sql clause...I've created interface for the create dynamic t-sql clause. But the clause should get data from relational database. It needs inner join...so it's very hard to do it..
for example
lkpProduct
id product 1 Mouse 2 Keyboard
3 Modem 4 Monitor
main
id productid
1 4 2 1 3 2 4 3 5 4
You can get this table with 2 ways like this..
1. Select main.id, lkpProduct.product from main,lkpProduct where main.productid = lkpProduct.id 2. Select id, case productid when 1 then 'Mouse'.....end as product from main
I have a stored procedure which expects one parameter @Company The variable @Company holds pipe delimited value: "CNN|AOL|ABC"
I need to build a WHERE clause by parsing @Company value, so the select will look like below:
SELECT * FROM Company WHERE CompanyName = 'CNN' AND CompanyName = 'AOL' AND CompanyName = 'ABC'
P.S I know that above select doesn€™t really make sense , but I have a bigger query that would be hard to explain in this topic so I just simplified it.
This fails with the error "cannot convert varchar to numeric". I believe since ColumnA is numeric, its trying to convert the dynamic paramter to numeric leading to the failure.
has someone implemented an In clause as a parameter? Please do not tell me that I can append the values as string and construct a dynamic query. I want to use a parameterized version. I will be calling this repeatedly and dont want recompile overhead.
I am trying rewrite an sp that I have that is considered dynamic cause it builds a where clause based on a bunch of if statements then adds it to the the end of select
i.e
if...@where = @where + ' llll '
if...@where = @where + ' llll '
select @statement = @statement + @where
exec(@statement)
I have rewritten most of it to but I have several conditions that use ' contains' for the condition and I can't get SQL server to recognize an if statement or a case statement.
Is it possible to use either statement inside a where clause??
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:
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.
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.
I've search around quite extensively on the net and found a few examples that touch on this subject, but the only definitive one that seemed to solve this problem used a temp table in the UDF, which, to my knowledge, is impossible...
The problem is thus: I want to create either a stored procedure or a user defined function to return a list of values I can intersperse to use in a WHERE AccountID IN (<values>). This way, if someone were to create a new stored procedure and they wanted to either only select accounts with those IDs or perform a NOT IN and use it to filter.
The Solution I'm attempting: My idea is best represented in psuedo-code: - Create a Function that stores all account Ids we relate to a particular account type, in this case, let's say accountsids "100, 101, 102, 407" are all accounts we want to consider "cash". - The function would look something like: CREATE FUNCTION CashAccountIDs()
RETURNS TABLE
AS
BEGIN DECLARE TABLE @t1 (account INT) INSERT INTO @t1 VALUES (100) INSERT INTO @t1 VALUES (101) INSERT INTO @t1 VALUES (102) INSERT INTO @t1 VALUES (407) RETURN @t1 END
Then I could call this function by doing something such as:
SELECT * FROM Accounts WHERE AccountId IN (dbo.CashAccountIds())
I would presumably do this for other collections of accounts as well, so that I would end up with say 5 functions I could call to filter various types of accounts.
Not too certain if I am approaching this the correct way or not, I've been receiving a myriad of errors trying different methods. If I use the function above it tells me "Must declare @t1", so I modified it so @t1 is declared in the RETURNS statement, and the syntax checks then work, but when I attempt to save the function it tells me "Cannot perform alter on fn_cashaccountids because it is an incompatible object type"
(The code I use to generate this error is: CREATE FUNCTION fn_cashaccountids ()
RETURNS @t1 TABLE (i INT)
AS
BEGIN INSERT INTO @t1 VALUES (100) RETURN END
Hopefully I've provided enough but not too much info to sift through, it seems to me this would be something encountered a bit before.
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
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!
I use a DataGrid to show the data, and I want it has a sorting and Paging function, so I use dataset to collect the data from middle tier function and stored procedure. I have code in aspx page like BindData(ViewState("SortExpr")).
In the stored procedure I pass SortExpr as parameter as following:
DECLARE @RowCount int SELECT @RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @Zipcode AND CityType = 'D'
if @RowCount > 0 BEGIN SELECT z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear, a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance /* The above functions requires the Distance Assistant. */ FROM ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r, AutoAd a WHERE z.Latitude <= r.MaxLat AND z.Latitude >= r.MinLat AND z.Longitude <= r.MaxLong AND z.Longitude >= r.MinLong AND z.CityType = 'D' AND z.ZIPCodeType <> 'M' AND z.ZIPCode = a.Zipcode AND a.AdActive = '1' AND a.AdExpiredate >= getdate() AND a.Make = @Make AND a.Model = IsNull(@Model,a.Model) AND a.Condition = IsNull(@Condition, a.Condition) AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles ORDER BY @SortExpr END ELSE SELECT -1 As ZIPCode --ZIP Code not found... GO
but I got the error as "variables are only allowed when ordering by an expression referenceing a column name". How I fix this error? Please help.
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?)
I am creating a dynamic query where i am appending a where clause something like -
IF (@CurCaptureDate IS NOT NULL) SET @where_clause = @where_clause + CHAR(10) + 'AND CD.CaptureDate = ' + @CurCaptureDate
PS - CD.CaptureDate is datetime and @CurCaptureDate is also datetime
but when executing , it gives this error - Msg 241, Level 16, State 1, Line 169 Conversion failed when converting date and/or time from character string.
i am not able to use convert here with these quotes.
i tried this - SET @where_clause = @where_clause + CHAR(10) + 'AND CD.CaptureDate = ' + CONVERT(VARCHAR(25),@CurCaptureDate )
but it makes it to -
AND CD.CaptureDate = Jul 19 2014 12:00AM. I would need the date in quotes.
I have to build a dynamic query to go over to Oracle and get some data and bring it back via a SQL Server Stored Procedure. How I can create an IN clause passing values and ","s from a SQL Server Temp Table that I have already built.
SET @SQLQuery = @SQLQuery + 'WHERE MEMBER.MEMBER_ID IN (''''' + SELECT MEMBER.MEMBER_ID + ',' FROM #TempTable + '''''Â '
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.
The first select is running fine but due to extra values added to the table the list of manual difined columns must be added manualy each time new values occur.
Is it possible to make the PIVOT's IN clause dynamicly as stated in the second script (it is based on the same table #source) when running it prompts the next error;
Msg 156, Level 15, State 1, Line 315 Incorrect syntax near the keyword 'select'. Msg 102, Level 15, State 1, Line 315 Incorrect syntax near ')'.
adding or moving ')' or '(' are not working.......
select * into #temp from #source pivot ( avg(value) for drive in ([C], [D], [E], [F], [G], [H], [T], [U], [V] )) as value select * from #temp order by .........
versus
select * into #temp from #source pivot ( avg(value) for drive in (select distinct(column) from #source)) as value
Arvind writes "i want to create a stored procedure returns an OUTPUT variable containing the no. of records given by a query, the query being dynamic. Preferrably the query should also be passed as a parameter to the stored procedure...If not,it should be constructed in the SP and a Part of the where clause is dependant on the value of another variable passed to the SP.
How should the query be constructed, executed, and then the Count(*) value returned?
"WHERE <condition1> AND <condition 2> ;
"AND <condition 2> " may exist or may not exist in the query; it is dependant."
