I created a stored procedure which perfoms a simple select like this:
CREATE PROCEDURE Reports_Category
(
@loc varchar(255), @pnum varchar(255)
)
AS
declare @vSQL nvarchar(1000)
set @vSQL = 'SELECT ' + @loc + ', ' + @pnum + ' FROM Category '
exec sp_executesql @vSQL
RETURN
GO
It takes field names as parameters. What happens is that when I supply value to only one parameter, the procedure gives error as it is expecting both values. Is there a way to make these parameters work like an 'OR' so that the procedure returns a dataset even if there is only one value supllied.
Using SQL Server 7 I am trying to modify an existing stored proc and make it more flexible. The below example represents the first part of that proc. The temp table that it should return is then used by another part of the proc (this query represents the foundation of my procedure). I need to figure a way to change the SQL Select statement, choosing between C.CONTRACTCODE and CB.EMPLOYERCODE on the fly. The query below will run but no records are returned. I am starting to believe/understand that I may not be able to use the @option variable the way I am currently.
I've tried creating two SQL statements, assigning them as strings to the @option variable, and using EXEC(@option). The only problem with this is that my temp table (#savingsdata1) goes out of scope as soon as the EXEC command is complete (which means I can not utilize the results for the rest of the procedure). Does anyone know how I can modify my procedure and incorporate the flexibility I've described?
Thanks,
Oliver
CREATE PROCEDURE test @ContractCode varchar(10), @dtFrom datetime, @dtTo datetime, @Umbrella int
AS
declare @option varchar(900)
if @umbrella = 0 set @option = 'c.contractcode' else set @option = 'cb.employercode'
select c.claimsno, c.attenddoctor, c.patientcode, p.sex, cb.employercode into #SavingsData1 from claimsa c inner join Patient p on c.patientcode = p.patientcode inner join claimsb cb on c.claimsno = cb.claimno where @option = @ContractCode and c.dateentered between @dtFrom and @dtTo and c.claimsno like 'P%' and p.sex in('M','F') and c.attenddoctor <> 'ZZZZ'
Is there any way to use conditional SQL in the SELECT clause of a stored procedure, i.e., use an IF-THEN to SELECT either COUNT(*) or a column-list based on the value of a parameter passed to the stored procedure? (I'd like to avoid having two sets of queries with the same WHERE clause).
I need to create a stored procedure for a report Web page which allows the user to specify different criteria. Is there a way to conditionally add different WHERE clauses as needed to a SQL SELECT statement?
I need to create a stored proc which has a conditional WHERE clause depending on the value of a passed parameter. I'm having trouble handling the condition. I'm missing something here.
I need to create a conditional if or case statement in SQL Server 2000 for a stored procedure. Basically if the value passed in is 1,2 or 3 then it will order by either NEWID(), a text field or a datetime feild.
Not done much dynamic sql so any help would be appreciated.
I want to return a subset of data from my 'items' table using a stored procedure based on a number of parameters as follows :
customer code varchar(8) userid varchar(10) status tinyint
where the customer code can be any valid customer code OR blank, the userid can be any valid userid or blank and the status can be 1 (item is open), 2 (item is closed) or 3 (item is open or closed, i.e. no filter on status)
Is there a better way to achieve this than the following disaster:
IF LEN(RTRIM(LTRIM(@custcode))) = 0 BEGIN IF LEN(RTRIM(LTRIM(@userid))) = 0 BEGIN IF @status = 1 BEGIN -- SELECT Query here !! END ELSE BEGIN IF @status = 2 BEGIN -- SELECT Query here !! END ELSE BEGIN -- SELECT Query here !! END END ELSE BEGIN IF @status = 1 BEGIN -- SELECT Query here !! END ELSE IF @status = 2 BEGIN -- SELECT Query here !! END ELSE BEGIN -- SELECT Query here !! END
END
END ELSE BEGIN IF LEN(RTRIM(LTRIM(@userid))) = 0 BEGIN IF @status = 1 BEGIN -- SELECT Query here !! END ELSE IF @status = 2 BEGIN -- SELECT Query here !! END ELSE BEGIN -- SELECT Query here !! END
END ELSE BEGIN IF @status = 1 BEGIN -- SELECT Query here !! END ELSE IF @status = 2 BEGIN -- SELECT Query here !! END ELSE BEGIN -- SELECT Query here !! END
I have a table that has incremental values, so the maximum value is the current total, however on occassion the value is reset and the increment begins anew. What I need to do is get the total the maximum value each time it is reset.
ie.
Value Date ----- ----- 12 1-1 14 1-2 100 1-8 35 1-10 50 1-12 5 1-15
Hello.Looking for a smarter way to code the following. I have a storedprocedure I will be passing several variables to. Some times, some ofthe fields used in a WHERE clause will not be passed, and I would liketo avoid having to code a bunch of if statements to set the executingcode. For example, below I would only like to execute the LIKEconditions only when the variable in question is not NULL. I did atest and if the variable is set to null, obviously the select does notreturn what I'm expecting.if @switch = "B"SELECT * from ikb whereikbtitle like @ins1 andikbtitle like @ins2 andikbtitle not like @ins3 andikbbody like @ins1 andikbbody like @ins2 andikbbody not like @ins3endThanks for any help or information with this.
I'm converting some direct SQL queries into stored procedures. The original queries have conditional parts appended depending on certain parameters like this:
Sql = "First part" If certain condition then Sql &= "Second part" Endif
How could I do this in a stored procedure? So far I have made them like this:
IF @condition > 0 Fist part Second part ELSE First part
You can see that I have to maintain 'First part' in two locations. What is a better way of dealing with this?
Created a stored procedure which returns Selected table from database. I pass variables, according to conditions
For some reason it is not returning any result for any condition
Stored Procedure
ALTER PROCEDURE dbo.StoredProcedure ( @condition varchar(20), @ID bigint, @date1 as datetime, @date2 as datetime )
AS /* SET NOCOUNT ON */ IF @condition LIKE 'all' SELECT CllientEventDetails.* FROM CllientEventDetails WHERE (ClientID = @ID)
IF @condition LIKE 'current_events' SELECT ClientEventDetails.* FROM ClientEventDetails WHERE (ClientID = @ID) AND (EventFrom <= ISNULL(@date1, EventFrom)) AND (EventTill >= ISNULL(@date1, EventTill))
IF @condition LIKE 'past_events' SELECT ClientEventDetails.* FROM ClientEventDetails WHERE (ClientID = @ID) AND (EventTill <= ISNULL(@date1, EventTill))
IF @condition LIKE 'upcoming_events' SELECT ClientEventDetails.* FROM ClientEventDetails WHERE (ClientID = @ID) AND (EventFrom >= ISNULL(@date1, EventFrom))
IF @condition LIKE '' SELECT CllientEventDetails.* FROM CllientEventDetails
RETURN
Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants
I have a data model with 7 tables and I'm trying to write a stored procedure for each table that allows four actions. Each stored procedure should have 4 parameters to allow a user to insert, select, update and delete a record from the table.
I want to have a stored procedure that can accept those 4 parameters so I only need to have one stored procedure per table instead of having 28 stored procedures for those 4 actions for 7 tables. I haven't found a good example online yet of conditional logic used in a stored procedure.
Is there a way to add a conditional logic IF statement to a stored procedure so if the parameter was INSERT, go run this statement, if it was UPDATE, go run this statement, etc?
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
Hello, Is there an SELECT statement to just return the last 100 row in my tables? I have about 500 rows in my tables and I only need the info on the last 100 rows. Thanks Steve
hello, i have a page "Picture.aspx?PictureID=4" i have a FormView witch shows details about that picture and uses a stored procedure with input parameter the "@PictureID" token from query string the Pictures table has among other rows "PictureID", "UserID" - uniqueidentifier - from witch user the picture belongs to i have a second FormView on the same page, witch should show "other pictures from the same user" and uses a Stored Procedure how should i write that stored procedure...frist to take the UserID from the picture with PictureID=4, then to pass it as input parameter and select the pictures witch has as owner the user with that UserID, and if can be done, to avoid showing the PictureID=4 again a solution should be to add at querry the UserID too, but i want to avoid that any sugestion is welcomed, please help me THANKS
Okay here is the deal. I need to take all data from tbl 1 and match it to data in 3 other tbls. I need to have it return everything back to me even if it is null.... IE tbl 1 I match the invoice_num to tbl2 site_id and then tbl2 marekt to tbl3 market. however even if tbl1 invoice_num dose not match tb2 site_id I still need to have it retun to a null value to the site_id. Here is what I have so far. This will return everything where the invoice_num and site_id match.
Code:
Select distinct t1.ID,t1.Deposit_date,t1.Ref_Num,t1.Company,t1.check_num,t1.Check_Date,t1.Check_Date,t1.Check_Total, t1.Individual_PMT,t1.Invoice_Num,t1.Invoice_Desc, t2.site_id,t3.CompanyCode,t3.CostCenter From( PMTK_tbl as t1 Left Join Leaseinfo as t2 on t2.site_id = t1.Invoice_Num) inner Join CostCenters AS t3 on t2.market = t3.market and t2.market_region = t3.RegionCode
Hi all, i'm new to SQL but i've been asked to write an SQL statement to select the latest numeric version value(in this case version 3) from this table, any help?
Hello all I create sp --------------------code---------------------- ALTER procedure [dbo].[uspInviteGroup] --uspInviteGroup 'fdi' @strUserId nvarchar(50)=null as select GroupName as 'strGroupName',GroupFounder as 'strGroupFounder' from SITE_MemberGroupswhere GroupId= (select GroupId from SITE_GroupMember where userId=@strUserId) --------------------code---------------------- but when I tested the above sp --uspInviteGroup 'fdi'return this error ------------------error--------------------- Msg 512, Level 16, State 1, Procedure uspInviteGroup, Line 6 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. ------------------error---------------------
in my case the second select statment return 2 value,I need the first select statment return two row how can I do that? thank you
Hello, Why when I make a custom select statment with multiple tables, that when I test my query I'm shown the same rolls multiple times. When I make a custom query with just one table everything works just fine. I don't have any primary keys or restraints or whatever. SELECT * FROM table1, table2, table3 Does not work(shows each row multiple times) SELECT * FROM table1 works just fine (shows each row just one time) Thanks Steve
I need to add some cases to the select statment for cpeorderstatus: Here is my Select statement: "SELECT O.ORDERID, C.FIRSTNAME, C.LASTNAME, O.CLIENTORDERID AS CRMORDERID, TO_CHAR(O.ORDERDATE, 'YYYYMMDD') AS CPEORDERDATE, TO_CHAR(O.SHIPDATE, 'YYYYMMDD') AS SHIPDATE, O.TRACKINGNBR AS TRACKINGNUMBER, O.SHIPNAME AS CARRIER, OI.ITEM AS CPEORDERTYPE, OI.QTY, O.STATUS AS CPEORDERSTATUS, OSN.ORD_SERIAL_NO AS SERIALNUMBER, C.BTN AS BTN, C.FIRSTNAME AS FIRST, C.LASTNAME AS LAST, C.SHIPADDR1 AS ADDRESSLINE1, C.SHIPADDR2 AS ADDRESSLINE2, C.CITY AS CITY, C.STATE AS STATE, C.ZIP AS ZIP, TO_CHAR(R.ISSUEDATE, 'YYYYMMDD') AS ISSUEDATE, R.RMA_ID AS RMANUMBER, R.RMA_REASON AS REASON, TO_CHAR(R.RETURNDATE, 'YYYYMMDD') AS RETURNDATE FROM SELF.ORDERS O, SELF.CUSTOMER C, SELF.ORDERITEM OI, SELF.ORD_SERIAL_NUMBER OSN, SELF.RMA R WHERE O.CUSTID = C.CUSTID AND O.ORDERID = OI.ORDERID AND O.ORDERID = OSN.ORDER_ID (+) AND O.ORDERID = R.ORDER_ID (+) AND (C.CUSTID IN (SELECT C.CUSTID FROM SELF.CUSTOMER C WHERE C.BTN='{0}')) ORDER BY O.ORDERDATE DESC" I need to add multiple cases to cpeorderstatus, five different cases. Cane anyonye HELP
Do any of you know if it's possible to get the name of your field fromanother table?E.g.Select FYear as [Select description from YearTable where category=1]I need the name of the fields to be user defined and the only way Ican see this happening is through a table. But now...how do I get thefield name from the table?Any help would be appreciated!!!!Thank you,Susan
Hi, I have a Command that I can not get to work. I am trying to take an AVERAGE of 1 Columb and display it in a label. ALSO what statement do I add when the Lable that is supposed to display that output is empty. Right now It gives me an Error.comm = New SqlCommand("SELECT * AVG Rating FROM Reviews WHERE CID = " & Request.QueryString("CID"), conn) Dim SqlDataAdapter1 As New SqlDataAdapter("SELECT AVG Rating FROM Company WHERE CID = " & Request.QueryString("CID"), conn)Dim objReader As SqlDataReader conn.Open() objReader = comm.ExecuteReader() objReader.Read()lblRanking.Text = objReader("Rating")
