I'm hoping someone out there has a creative solution for my scenario. I have the report code below. I want to create a parameter that allows the user to select which where statement to use in the report. Then I want to use the corresponding where description below ( ex: @WHEREDESC1 ) in my header which will describe (in laymen's terms) which parameter was chosen and what is being reported on.
Has anyone achieved this or have an idea how it may be done?
I have a query with 17 separate, optional, parameters. I have declared each parameter = NULL so that I can test for NULL in the case that the user didn€™t not pass in the parameter.
I am new enough to SQL Server that I am having difficulty building the WHERE clause with all of these optional parameters.
One solution I was advised on by a well paid SQL programmer, was to use a string in the stored proc and dynamically build the WHERE clause and exec it at the end of the sp. But the whole point of a stored proc is that it can be compiled and cached to make it faster, yet the string approach makes it have to compile every time it€™s run! Not a good solution, but maybe it€™s the best I can do . . .
I have tried many different approaches using different functions, etc. but I€™ve hit a brick wall. Any help in sorting it out with YOUR techniques would be greatly appreciated:
1. To add the parameter to the WHERE clause and test for NULL I€™ve used the COALESCE function such as €œWHERE table.fieldname = COALESCE(@Param, table.fieldname)€?. This works well if there is only one item in the parameter, but in the case that I pass multiple items to the parameter, it completely fails.
2. To handle multiple items, for example, if @Param = €˜3,7,98€™ (essentially, a csv separated list of keys)
doesn€™t work because @Param needs to be parsed from a string into an array of integers in the parameter. So, I am using a UDF I discovered to parse the multi-item parameter. The UDF can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp and it returns a table variable that can be used in an IN statement. So I€™m using
Code SnippetISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@Param,€™,€™))
which works brilliantly in my WHERE statement AS LONG AS @Param ISN€™T NULL. So how do I test for NULL first and still use this approach to multi-item parameters?
I€™ve tried
Code SnippetWHERE @Param IS NULL OR ISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@Param,€™,€™))
and though it works, the OR causes it to slow way down as it compares every record for the OR. (It slows down by approximately 800%.) The other thing I tried was
Code SnippetISNULL (table.fieldname, 0) IN (CASE WHEN @Param IS NULL THEN ISNULL(table.fieldname, 0) ELSE (SELECT value FROM dbo.fn_Split(@Param,€™,€™)))
This fails with €œSubquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression€? due to the multiple values in the parameter. (I can€™t understand why the line without the CASE statement works, but the CASE line doesn€™t!)
Am I even on the right track, cuz this is driving me mad and I just need a way to deal with optional multi-item parameters in an IN statement? HELP!
I want the below sql code to force a error if it falls on a saturday or sunday, and there are no rows in the OrderTrans table for the previous day (TransDate) but not sure how to do multiple if statements.
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATE TABLE [dbo].[OrderTrans]( [OrderId] [int] NOT NULL,
Parameter Information cannot be derived from SQL statements with sub-select queries. Set Parameter information before preparing command.
Here's the query:
update GCDE_SEQ set LAST_NO = (select max(FLD_NO) from PONL_FLD) ,UPDT_USER = ? ,UPDT_DT = getdate() where SEQ_NM = 'FLD_NO'
Why can't Execute SQL Task handle this simple query? I figure i can use 2 SQL Execute SQL Task, one to get the max into a var, and the other to do the updating. However, this is alot of trouble since i'm having this almost exact query in alot of places. Any way around this?
I am writing a fairly complex data automation procedure that basically uses about 5 stored procedures and some additional sql from C# to populate a database. i would like to utilize transactions in C# and sql to help me if something goes wrong. My question is, do i have to maintain the same connection in order to have one transaction for all my Commands. right now i do things like connection.Open();Command.ExecuteNonQuery(); connection.Close(); connection.Open();Command2.ExecuteNonQuery(); connection.Close(); i think i have to keep the same connection open for the duration of all my commands in order to utilize transactions, is this correct? thanks, mcm
Is there a way to execute multiple SQL statements within SQL Server? I'm triyng to write a sql statement that will delete from three tables. I need at least two sql statements to delete from these three tables. Can this be done?
how can take codes below and put them into one store procedure to supplie a gridview. also i will like to define the row name on the left like i did to the column on the top using the 'AS' Code1.... SELECT SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February, SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April, SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June, SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August, SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October, SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December, SUM(CASE WHEN site_descr = 'SITE1' THEN 1 ELSE 0 END) AS AllTotal FROM dbo.V_results WHERE (site_descr = 'SITE1')
Code2.....
SELECT SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February, SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April, SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June, SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August, SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October, SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December, SUM(CASE WHEN site_descr = 'SITE2' THEN 1 ELSE 0 END) AS AllTotal FROM dbo.V_results WHERE (site_descr = 'SITE2')
Hi guys and gals, I am trying to create a select statement that will return an INT that I will later have to use in another select statement. I have the following code, however, I keep getting an error that says: 'Error116: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.' My Code is below: //Start of sql CREATE PROCEDURE ADMIN_GetSingleUsers( @userID int) AS DECLARE @userSQL intSET @userSQL = (SELECT User_ID, TITLE.TITLE AS TITLE, Cast(Users.Active as varchar(50)) as Active, Cast(Users.Approved as varchar(50)) as Approved, Users.Unit_ID As usersUnitID, * From TITLE, Users WHERE User_ID = @userID AND TITLE.TITLE_ID = Users.Title_ID ) Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @userSQL //End of sql Can you point to what I am doing wrong? Thanks in advance!
This query brings back 2 rows for each record, one for each case statement. How can I change this to bring in the contsupp.notes for both types of data? Or could it be fixed in the joins?
select contact1.accountno, contact1.key5 as "Ch#", contact2.uexpsort as "Sort", contact1.company as "Church", contact1.contact as "Editor", contact1.phone1 as "Phone", contact2.uemerg as "Emergency", contact1.Address1 as "Address", contact1.city as "City", contact1.state as "State", contact1.zip as "Zip", contact2.ubulletnqt as "Qty", contact2.ubullcolor as "BullColor", contact2.ubarcode as "Barcode", contact2.udelivery as "Delivery", contact2.uoutputdev as "OutputDev", contact2.utimedeliv as "Time", contact2.ucolor as "DelivColor", contact2.ucollated as "Collated", contact2.ustapled as "Stapled", case when contsupp.contsupref = 'Delivery Notes' then contsupp.notes end as "Deliverynotes", case when contsupp.contsupref = 'Cover Change Slip' then contsupp.notes end as "CoverChangeSlip"
from Contact1 inner join contact2 on Contact1.Accountno = Contact2.Accountno inner join contsupp on Contact1.Accountno=Contsupp.Accountno where contact1.key5 not like '' and contsupp.contsupref='Delivery Notes' or contsupp.contsupref ='Cover Change Slip' order by contact1.key5
I have written one CTE (common table expression) and trying to use same CTE with three seperate UPDATE statements which gives me error saying "Invalid Object name" (it works fine when I try to use with 1 update statement (any one from three update statements)
Isnt it possible that I can use 1 CTE with mutiple update statements?
Does anyone know how to execute more than one MDX statement in a single report? For example, If you wanted to create a session set, run some mdx with the set included, then drop the session set. In Management studio the mdx works perfectly if you separate the individual mdx statements with GO. I tried using semicolons as mdx statement terminators and this didn't work either.
Reporting Services does something between when the report begins it's execution and the time the MDX is submitted to AS. And running a trace to see exactly what is passed in doesn't work either - the only message displayed is the same error message as what is passed back to RS. (I ran a trace with all events selected).
I am not even sure if this is possible but I want to be able to excute several statements (or SPs) at once from inside a SPs (or any other method).
What I am doing is a I am taking data from a single column, multiple rows and making it into one row (i.e. data1 + data2 + data3....) But I am doing this to a total of 2.1 million individual rows and the result will be about 204k rows.... what I have written is basically a nested loop and it works fine but very slow... slow as in it has been running for 24 hours now and it is about 60% done... I need this to finish in under 36 hours preferably...
If I could handle more than one set of data at once (there will be no duplicates) I could speed up the process by how ever many I feel like working with...
So is there a way to execute a statement (sp or function) and go to the next statement without waiting for the first to finish??
Hello. I'm trying to reduce some code in my stored procedure and I'm running into lots of errors. I'm somewhat of a novice with SQL and stored procedures so any help would be beneficial. I have a SP that gets a page of user data and is also called when sorting by one of the columns (this data is placed in a repeater, btw). I quickly learned that I wasn't able to pass in string parameters the way I had hoped in order to handle the ORDER BY and direction (ASC/DESC) so I'm trying to work around this. So far I've tried the following with many errors.WITH Users AS ( SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @OrderBy='FirstName' AND @Direction='DESC' THEN (FirstName + ' DESC') WHEN @OrderBy='FirstName' THEN FirstName WHEN @OrderBy='LastName' AND @Direction='DESC' THEN (LastName + ' DESC') WHEN @OrderBy='LastName' THEN LastName END ) AS Row, UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName FROM vRF_Users )
SELECT UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName FROM Users WHERE Row BETWEEN @StartRowIndex AND @EndRowIndex
I've tried a combination of similar things with parenthesises, without, doing "THEN FirstName DESC" without concatenating anything, etc. I also tried: DECLARE @OrderByDirection varchar(32) DECLARE @DESC varchar(4) SET @DESC = ' DESC'
IF @Direction = 'DESC' BEGIN SET @OrderByDirection = (@OrderBy + @DESC) END And then writing my case statemet like this:ORDER BY CASE WHEN @Direction='DESC' THEN @OrderByDirection ELSE @OrderBy ENDObviously this didn't work either. Is there any way to gracefully accomplish this or do I just have to use a bunch of if/else statements and lots of redundant code to evaluate all my @OrderBy and @Direction parameters???
I was wondering if it's possible to have a stored procedure that has two select statements which you can combine as a single result set. For instance:select name, age, titlefrom tableaselect name, age, titlefrom tablebCould you combine these queries into a single result set?
I am almost sure I can update variables columns in one select/case type statement, but having problems working out the syntax.
I have a table with transactions - with tran types as the key.
in this example, types = A,B,C ,D.
in this first example I am updating the sum of QTY to value t_A based on tran types =A.
can I perform sub query/case to update with the same where clause but for types B,C and D?? I also have to insert for specific lot numbers each sum values.
Create table #t_reconcile( t_lot_number int not null, t_A float, t_B float, t_C float, t_D float)
insert #t_reconcile
select t.lot_number, sum(t.qty) from i , t where i._id = t.event_id i.transaction_type = 'A' group by t.lot_number order by t.lot_number
Hi,I am wondering if anyone has any examples of how to run multiple sql statements from a file using .net? I want to automatically install any stored procedures or scripts from a single file on the server when my web application runs for the first time. Thanks for your help in advance!
I'm working on a query which involves changing the case of a field from mixed case to all lower case. The field exists in multiple tables, so to do this I have multiple update statements. Is there a way to make this more efficient?
See below for example:
update InvestBroker set BrokerID = lower(BrokerID)
update InvestFill set BrokerID = lower(BrokerID)
update InvestBrokerAccount set BrokerID = lower(BrokerID)
update InvestFIXBroker set BrokerID = lower(BrokerID)
update InvestUploadBrokerFilter set BrokerID = lower(BrokerID)
update InvestSettleInstructions set BrokerID = lower(BrokerID)
How do I enclose multiple filters in a variable, for instance how would I put the following filter into a variable and also is it actually possible or do I have to do something else before performing this type of operation:
tel1 like '072%' or tel1 like '+27 72%' or tel1 like '072-%' or tel1 like '+2772%' or tel1 like '72%' and len(tel1) > 7 or tel2 like '072%' or tel2 like '+27 72%' or tel2 like '072-%' or tel2 like '+2772%' or tel2 like '72%' and len(tel2) > 7 or tel3 like '072%' or tel3 like '+27 72%' or tel3 like '072-%' or tel3 like '+2772%' or tel3 like '72%' and len(tel3) > 7 or tel4 like '072%' or tel4 like '+27 72%' or tel4 like '072-%' or tel4 like '+2772%' or tel4 like '72%' and len(tel4) > 7 or tel5 like '072%' or tel5 like '+27 72%' or tel5 like '072-%' or tel5 like '+2772%' or tel5 like '72%' and len(tel5) > 7 or tel_other like '072%' or tel_other like '+27 72%' or tel_other like '072-%' or tel_other like '+2772%' or tel_other like '72%' and len(tel_other) > 7
the problem is that it's got a couple of apostrophes which when declaring variables pulls it out of that mode, I have looked on the internet but can't seem to find anything
I know I should know the answer to this, but I just can't quite get the syntax down
Code: Select case when zipCode = '10185' Then 'Deliver' Else when zipCode = '2309' And paid = 'Yes' Then 'Deliver' Else When zipCode = '1291' And paid = 'Yes' Then 'Deliver' Else When zipCode = '88221' And paid = 'No' Then 'Hold' Else when zipCode = '34123' Then 'Deliver' End From postalDeliveryDatabase
I believe we can you multiple statements in stored procedures?
Suppose I have a stored procedure and I pass parameters to this SP. What I am aiming for is to pass some values to the stored procedure, use a select statement to retrieve some values, then have two update statements as below. Updating the same table but with opposite values, both passed as a parameter and retrived, as given below:
Hi!I got 2 stored procedure, proc1 executes proc2,proc2 does some updates and inserts on different tables ...proc1:ALTER PROCEDUREASexecute proc2SELECT * FROM tblFoo______________________my problem is, that when executing proc1, I receive the message:"THE SP executed successfully, but did not return records!"But I need the resultset from "SELECT * FROM tblFoo" that is executedat the end of proc1.I'm not sure, but I think that I solved a similira problem with "setnocount on", I put it into both SP, but it's still the same ... noresultset ...How can I display "SELECT * FROM tblFoo" within a SP, where SQLstatements are executed before?!Thank you!
I am curious as to whether I can insert more than one IIF statement into a table's visibility expression without nesting one inside the other.
I am rather loath to do so because of the risks that subsequent developers unfamiliar with both the situation and the logic could alter one IIF and not realize that if chokes off the other.
It had been my hope to apply the IIFs such that they would act independent of one another in a sort-of 'OR' statement whereby if one resulted in the table's hidden property being 'True' then it would not matter if the other's was true or false, etc.
So I'm thinking if I can have multiple statements within the CASE-THEN..or do I have to CASE out each individually? Kind of like this....
CASE WHEN [AddressType] = 'M' THEN [MailingAddress].[Address1] [MailingAddress].[Address2] [MailingAddress].[City] [MailingAddress].[State] [MailingAddress].[Zipcode] WHEN [AddressType] = 'D' THEN [DefaultAddress].[Address1] [DefaultAddress].[Address2] [DefaultAddress].[City] [DefaultAddress].[State] [DefaultAddress].[Zipcode]
Hi,We are discussing possible implementation for sql2005 database(s). This database will serve one web portal. Part of data will get into it by hand, and part will be replicated from internal system.Some of us are for creating two separate databases, since there are two separate datasources. One, automatic, will change very little over time and requires almost no maintenance. Other datasource will be manual input. Tables and procedures related to this part will change over time.Some of us are for creating single database, since it will serve one web site. More important this group is concerned about performance issues since almost every select will require join between tables that would be stored in two separate databases. Do these issues exist? Can you share some insights, comments, links about this?
I have a SP that has the correct syntax. However when I run my web-app it gives me this error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. " The procedure takes in three parameters and retrieves 23 values from the DB to display on my form. Any ideas?
Hi, OK, trying to return the results from two SQL statements into a DataSet using SqlDataAdapter. The SELECT statements query the same table but are looking for different records based on the date that the records were inserted - the 1st query looks for records fro the current month and the 2nd one looks at the same records but for the previous month. The goal is to be able to do some math within a repeater and get the difference between the two records. Sounds easy enough and it has worked for me in different variations of the same idea but not this time - here's the code: Protected Sub buildPartsReport(ByVal varHC) objConn.Open() sSQL = "SELECT ap.id,item_model,item_sn,aircraft_id,item_loc=item_type+ ' on ' +(SELECT tnum FROM T_Aircraft WHERE id=aircraft_id),apt.tot_time As endTimes,apt.tot_cycles as endCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & rD & "' AND item_type LIKE 'engine%';SELECT ap.id,apt.part_id,apt.tot_time as startTimes,apt.tot_cycles as startCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & oldRD & "' AND item_type LIKE 'engine%'" Dim objCommand As New SqlDataAdapter(sSQL, objConn) DS = New DataSet() objCommand.Fill(DS) Repeater1.DataSource = DS Repeater1.DataBind() DS.Dispose() objCommand.Dispose() objConn.Close() End Sub Sorry if it wrapped a bit. The "rD" and "oldRD" are the variables for the date ranges (currently set to static numbers for testing). I'm getting the following error when I run this on an ASP.Net page: System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'startTimes'. The code works fine when run via the Query Tool on the SQL server (SQL 2005 Std) though it produces two distinct "tables" which I'm guessing is the problem. I've tried variations on the code including creating a 2nd dataset and then attempting a merge (no joy) and I've tried the ".TableName" route but it complains about trying to add the tablename twice. Thoughts? I need to get this to work - it is part of a reporting component for an application that I'm developing and I'm stuck. Thanks as always...
Hi, I have a table containing SQl statements. I need to extract the statements and execute them through stored procedure(have any better ideas?)
Table Test
Id Description
1Insert into test(Id,Name) Values (1,'Ron') 2Update Test Set Name = 'Robert' where Id = 1 3Delete from Test where Id = 1
In my stored procedure, i want to execute the above statements in the order they were inserted into the table. Can Someone shed some light on how to execute multiple sql statements in a stored procedure. Thanks
first of all is it possible? if so, what am i doing wrong with this
INSERT into TB2
(
ClientCode, EngagementCode, EngagementDescription
)
SELECT (SELECT dbo.tarCustomer.CustID FROM dbo.tPA00175 INNER JOIN dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey),
NULL,
SELECT (SELECT dbo.tPA00175.chrJobNumber FROM dbo.tPA00175 INNER JOIN dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey)
the first select statement for works fine, but the second one and all after i get a syntax error near 'select'.
this is just a shortened version of the statement. how would i run select statements for a table to be inserted into with different column names. also with items that are hard coded like the 'null'. thanks
Hi All I'm Scripting various Upgrade scripts that set up roles / users / create databases etc and want to Exit the Script unconditionally if for example the user already exists etc. I can't Use RETURN because it just jumps to the Code following the next GO statement There is also some USE [Database] Statements Is there a way to do this or am I missing something fundamental here ? GW
