Hi, I was wondering if someone could help out. I need to create a stored procedure, but before i do so, I need to know if I can store a conditional expression in a string and just use that variable as my condition for the WHERE clause. The reason I ask this is because I am trying to create a stored procedure that queries different things depending on what the inputs are.
View 2 RepliesMy table is as follows and I have a string ‘U2’,’U4’
CustomerID UserID OtherUsers
C1U1#U3~#U5~
C2U2
C3U3
C4U4#U1~#U2~
C5U5
C6U1#U2~#U5~
C7U2
C8U3#U1~#U4~
Now I should issue a sql which should extract the CustomerID where UserID in (‘U2’,’U4’) or (U2 or U4) in OtherUsers.
i.e I should get the CustomerIDs C2,C4,C6,C8
I have trouble in coming out with the sql. Does anyone have idea how to achieve this?
I am creating a SP below and it keep saying must declare scalar variable @comments,I have already declared @comments varchar (255)
error---> (Msg 137, Level 15, State 2, Procedure CheckPatientComments, Line 26
Must declare the scalar variable "@commnets".
)
quote:
CREATE PROCEDURE [dbo].[CheckPatientComments] (@enc_id varchar(36) OUTPUT, @data_ind CHAR(1) OUTPUT)
AS
BEGIN
DECLARE
@comments varchar(255)
[code]....
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
Example:
DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'
SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'
As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.
I am trying to add a where condition on an ID column(type - INT) with values coming from a variable (type - STRING). i am using cast to cast the ID as Varchar and then apply the condition, but i am not getting any results back. following is an example of what i am trying to do.using temp table in the example , so you can copy the t-sql and run as is.
CREATE TABLE #TABLE1(ID INT)
INSERT INTO #TABLE1 VALUES (1), (2) , (3) , (4)
DECLARE @ID varchar(8000) = '2,4'
[code]....
I have a string variable and following data.
Declare @ServiceID varchar(200)
set @ServiceID='change chock','change starter','wiring for lights','servicing'
when I assign values in @ServiceID Â in the above manner then it shows error. How to get string array in @ServiceID variable so that i can go ahead.
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
Dan
Hi,
I am creating a stored procedure which takes a few paramaters. One of which is a string of comma separated codes. At the end of my select query the codes will be used in the where clause.
What I'm doing is parsing the comma separated string and populating a temp table called #codes. So my query will look something like this:
select * from tableA
where tableA.col1 = 'something'
and tableA.code in (select * from #codes)
and....
However, the code paramater can be null and if this is the case I want the query to be called for all codes - i.e. I effectively want to remove the and tableA.code in (select * from #codes) part of the where clause.
Is there any clever way of doin this other than having a if... else... and writing 2 queries - one with the and and one without it?
Hope this is clear.
Thanks,
Wallace
Hello all,
I've got some problem with the following query:
select
subscriber_id, sum(convert(int,n_inboundcalls)) as totalinbound
from calldiversityratedaily
where totalInbound < 10
group by subscriber_id
The error message provided is:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'totalInbound'.
I'm using MSSQL server 2000, version 8.00.194 and it appears that I can't use the name I give to the sum in the where clause.
Could anyone help me please?
create function stzipcustinfo
( @campaign varchar(50), @startdate datetime, @enddate datetime,@audience_type char(10) )
RETURNS TABLE
AS
RETURN
(Select distinct t.campaign,t.Sopnumbe,t.Custnmbr,t.Custname,
t.Address1,t.Address2,t.City,t.State,t.Country,t.Zipcode, t.Phone,sum(t.totalquantity) as Totalquantity,
t.Audience_type
from
(
select distinct
v.sopnumbe, v.campaign, v.custnmbr, v.custname, v.address1, v.address2,v.city,v.state,v.country,v.zipcode,
v.Phone, v.totalquantity as totalquantity,
case @audience_type
when v.custclas then v.custclas
end as audience_type
from vwstzipcustaudienceinfo v
Where (v.itemnmbr = '' or v.itemnmbr=v.itemnmbr) and v.Campaign = @Campaign and (v.docdate between @startdate and @enddate)
) as t
where t.audience_type is not null
group by t.campaign,t.Sopnumbe,t.Custnmbr,t.Custname,
t.Address1,t.Address2,t.City,t.State,t.Country,t.Zipcode, t.Phone, t.Audience_type
)
--select * from mystatezipcustaudienceinfo('cpd','1/1/2008','5/15/2008','INDPATIENT') --getting 500rows
--select * from mystatezipcustaudienceinfo('Cpd'','1/1/2008','5/15/2008','INST-HOSPITAL') -- note getting single row
problem have a “-“ in them..i m not getting result..so what condition should be in red color one..
thanks
I do have a table type as input
---------------------------
num|columnname|value
---------------------------
1|Name|3X5900
2|employeeID|null
3|JOD|null
4|Address|null
From this I have to form the where clause like "Name like'3X5900' ".This will be dynamic , as the search may vary on the next call for search.
How I have to implement this dynamic query in the below mentioned code.I have add the dynamic query where i have mentioned as '---- Where clause has to be added '. Sometime the table input will be having all the value for search. How I can implement this in my query with good performance.
DECLARE @Where varchar(4000);
Select @where = 'v.Name LIKE ''3X5900'''
SELECT * from
(SELECT Row_number()Over(Order By V.ProjectId) Rownum,
v.Firstname,
[Code] ....
How can update / insert records from table1 colum to table2 colum
with condition ?
I want to insert/update records like this.
update employee
set joine_date = select joine_date from master_table
where emp_id.employee=emp_id.master_table
regards
Martin
Hi,
hope someone can help or suggest something to help me with the issue.
I have a list of projects. this list contains all master (parent) and all subprojects(child). when I click on a project I want to be able to retreive information about that project and it's subprojects. here is my delema. I want my sql query to check if project is master or sub. if master then get all data for this project and its subprojects but if it's sub then get data only for that sub. below is a sample data that I hope it clear things up
parent_ID child_id Type projname
-----------------------------------------------
100 100 P parent_X_proj
100 25 C child_X_proj
100 29 C child_X_proj2
200 200 P parent_Z
300 300 P parent_Y
etc................
this is how my table is constructed. my application passes child_id and what I want is if someone clicks on parent_X_proj I want to be able to retreive the three projects (100,25,29) but if someone clicks on child project (29) then I want only that project.
so I want my query to look for the type and if my type is P (parent) then get all project where parent_id = 100(for example) but if type= C then get child_id = 29.
I know it could be done in stored procedure buy my application cannon executre SP but only sql statements.
Thank you for any help
Hi all,
i have a query as below that creates a count in the field Total. I wanted to then be able to say only show me where there are more than 2 incidents. But i can't get it to recognise my created field. Any advice?
Thanks.
select a.vchcompanyname, count(*) as total
from company a inner join incident b
on b.iownerid = a.icompanyid
where b.iincidentcategory = 1
and a.icompanytypecode = 102165
--and total > 2 (Wont recognise Total)
group by b.iownerid, a.icompanyid, a.vchcompanyname
order by 2 desc
First I have to apologize about posting the problem here , since it not actually relate to SQL Server
I am really new to database programming.
I use ADO (the ActiveX one , Not ADO.NET) In an MFC Application to access MS ACCESS Database
I can insert date in to table using statement like this
insert into tableXXX (date_field) values ('1/2/2007')
but when i tried to query it using this statement
select * from tableXXX where date_field = #1/2/2007#
Nothing return
I also tried to use #1-2-2007# , #01-02-2007# but it all the same
Can someone tell me how to successfuly use date as a query condition ?
Thank in Advance
Hi there,
Following is the code I have, which I select and execute as one batch.
declare @intData integer
Declare @strSQL varchar(8000)
Set @strSql = ''
set @strSql = 'select @intData = Age from MasterRecords'
exec (@strSql)
I get the following error in SQL 2000's query analyzer.
Must declare the variable '@intdata'.
Any and all help is highly appreciated.
Hi,
Does anyone know what is the maximum size for a string variable that can be used by Execute statement in SQL 7 and SQL 6.5? If I really need to execute
a long statement around 9000 characters long, how can I do it?
declare lcsql varchar(x) && what is the maximum size for x in SQL 7 and SQL 6.5
select lcsql = 'select ...'
execute (lcsql)
Hi
I have a problem with a string variable which looks something like this
@VARIABLE = 'RTY,YTUK,GHJ'
I need to be able to select (RTY) the first individual values seperated by the commas to be used as a variable elsewhere in my SQL script.
Following this I need to use the second value (YTUK) to be run in the same piece of code. Followed by the 3rd value (GHJ)
Can anyone tell me how to do this?
Thanks
Hi all,
I have to write a select query which need some logic to be implemented.
Query is like
select name,number,active,
if active ="true" then
select @days=days from tbl_shdsheet
else
@days=''
end
from tbl_emp
In the above query there will be days row for that employee if active is true else there won't be any data for that emp in the tbl_shdsheet
So how can i write queery for this.
I have one table which contines 3 columns they are
1.emp
2.monthyear
3.amount
in that table i am having 6 rows like below.
emp monthyear amount
1 102013 1000
2 102013 1000
1 112013 1000
1 112013 1000
2 122013 1000
2 122013 0000
i want a total on group by condition on each employee. which will have the data from NOV to dec 2013 only.
out put should be like this
1 2000
2 1000
I wish to make a query with if condition Implemented in a database sql server 2008 R2.
I would like to set up a system FEFO (first expired first out) based on batch number based on the dates of Lapsed but since I struggle to put my request in place. The principle of my request is:
if amount of movement (QTEMVT)> = amount entered by the user via a user interface then withdraws the amount entered by the user in the batch (NUMLOT) the amount of movement of the item that lapses the first (execution of my request).
else if amount of movement (QTEMVT) <amount entered by the user
then the difference between the amount entered by the user and the amount of movement (QTEMVT) (execution of my request) and the following conditions:
the amount of movement (QTEMVT) = the amount of movement (QTEMVT) of this item stored in my database and the amount of movement (QTEMVT) <> 0
by taking the difference of the item requested directly from the batch (NUMLOT) of the item directly after lapses.
Basically I want to set up an item management query based on batch number and expiration dates.
ps: QTEMVT = quantity of the item stored in my database
NUMLOT = batch number items
DATFABRIC = manufacturing date items
DATPEREMP = expiry date items
Here is my request:
SELECT f.NUMLOT,f.DATFABRIC,f.DATPEREMP,f.QTEMVT
FROM FAIRE f INNER JOIN mouvement m ON f.CODMVT = m.CODMVT
INNER JOIN TYPE_MOUVEMENT tm ON tm.CODTYPMVT = m.CODTYPMVT
INNER JOIN SOUS_SITE ss ON ss.CODSOUSIT = m.CODSOUSIT
INNER JOIN SITE s ON s.CODSITE = ss.CODSITE
[Code] ....
Is it possible to stop a query from running if a condition is not met?
I have a stored procedure that returns some email addresses. A condition is that a load of new data should have happened in the current month.
If a load of new data hasn't happened I want the stored procedure to stop and return an error message instead of returning the email addresses.
I have a variable that finds the maximum data load date and I thought I could use an IF... ELSE... to check if new data had been loaded. Like this:
DECLARE @MaxLoadDate as date = (select max(load_date)
from #table)
IF @MaxLoadDate<Dateadd(month, Datediff(month, 0, Getdate()), 0) --First Day of Current Month
<Something here to kill the query>
ELSE
SELECT email_address
FROM... etc
I've tried a couple of things like PRINT 'Error', and SET NO EXEC ON, but the query seems to happily carry on past the IF condition and return the email addresses. I know that data hasn't been loaded this month, so it should fail.
following is a working code
declare @dte as datetime='2015-04-01'
declare @StDt as Datetime = DATEADD(mm,DATEDIFF(mm,0,@dte), 0)
declare @EnDt as datetime = DATEADD( DD, -1, DATEADD(mm,DATEDIFF(mm,0,@dte) + 1, 0));
DECLARE @query AS NVARCHAR(MAX);
[code]...
iam getting the ouput correctly my requirement is i want to write a condition here
JOIN busdetails b ON m.bus_id = b.bus_id
i want to write this statement as
JOIN busdetails b ON m.bus_id = b.bus_id and m.uname='lekshmi'
when i tried this code iam getting error
Good day,
The following query performs acceptably (2 seconds against 126,000,000 rows in the main table):
SELECT Count(*)
FROM
Message1_2_3 INNER JOIN
VDMVDO ON Message1_2_3.VDMVDO_ID = VDMVDO.VDMVDO_ID INNER JOIN
NMEA ON VDMVDO.NMEA_ID = NMEA.NMEA_ID
WHERE
NMEA.NMEA_ID BETWEEN 14000000 AND 14086000 AND
VDMVDO.RepeatIndicator = 0 AND
NMEA.SentenceFormatterID = 'VDM'
When we change the first condition from an Int column to a DateTime as in:
NMEA.TimeDate BETWEEN CONVERT(DATETIME, '2007-07-09 8:30:00', 102) AND CONVERT(DATETIME, '2007-07-09 9:30:00', 102)
the query performance falls to 14 seconds, even though both columns are indexed and a similar number of rows are found. When the select clause changes from a simple Count to a complex Max expression, response time falls to over a minute!
Any thoughs on optimizing the DateTime search would be greatly appreciated...
I've a complex view on a SQL 2014 Enterprise Edition. If I query the view with:
SELECT * FROM myComplexView
it takes 14 seconds to completes
if I want a subset of the result and I run the query with a WHERE clause:
SELECT * FROM myComplexView WHERE [Season]='A16'
The query never completes (I've waited 10 minutes and then cancelled the task).
I have a query where i would calculate the counts of three ServersÂ
declare @Managed float
declare @Active float
DECLARE @Values varchar(1000)
SET @Values = 'WE,EE,CO'
select @Managed = count(Name0) from v_R_System where Operating_System_Name_and0 like '%Workstation%'Â
[Code] ...
Here i need output like below
Workgroup Managed Active
'WE' 255 ,400
'EE' 300 ,450
'CO' 155, 600
So how to use these three values in the where condition when i use the where clause i have put in condition it will give me the subquery returns more than one value,so how should i use this scenario to accomplish this output?
hi there,
i am trying to pass a string which contains a string,
here is the code which is wrong :
{string sqlcommand = "select pnia.pnia_number, pnia.user_name, pnia.date_pnia, pnia.user_pnia, problem.problem, gormim.gorem_name, status.status_name from pnia,gormim,problem,status where (pnia.status='@p1' and status.status='@p1' and pnia.problem=problem.problem_num and pnia.gorem=gormim.gorem)";
OleDbCommand cmd = new OleDbCommand(sqlcommand,con);OleDbParameter p1 = new OleDbParameter("@p1",this.DropDownList4.SelectedItem.Value.ToString());
cmd.Parameters.Add(p1);
}
the problem is that the sql compailer doesnt take the parameter (@p1) as a string
if someone could help me with that it would be great ! tnx
Using SQL Server 7.0, I am trying to use a variable string called from a form to perform a simple query.
Here's what it looks like:
[after submitting a form on the previous page with a drop down list box titled "fiscal"]
dim strYear
strYear = Request.Form("fiscal")
sql = "SELECT * FROM VENDOR WHERE STATUS = 'P' AND '"& strYear &"';"
[where strYear is the following: PYMNT_DT > ''12/31/98'']
Records exist that meet this criteria. If the PYMNT_DT > '12/31/98' is hardcoded into the sql statement, 12 records are returned. However, when the variable string is used instead, no records are found. Any ideas on how this problem might be fixed?
Thanks in advance.
Sincerely,
Chad Massie
I want to run the following code:
Declare@Testnames as Varchar(500)
Set@Testnames = ('CT,MRI')-- List all radiology type
SELECT*
FROMRPT_OUTPATIENT_IMAGING
WHEREservice_level2 In (@Testnames)
Nothing comes back.
When I run:
SELECT*
FROMRPT_OUTPATIENT_IMAGING
WHEREservice_level2 In ('CT','MRI')
Then I get results.
Is there a way around the character limit for a variable? I'm throwing the following in a variable and evaulating it as an expression and having an execute sql task do it, but I can clearly see it's being cut off.
"insert work.dbo.data_run select publisher,publisher_db,subscriber,subscriber_db,article,null,getdate(),null
from msdb.dbo.sysreplicationalerts
where error_id<>0
and alert_error_code=20574
and [time] between '" +(DT_STR,50,1252)@[System::ContainerStartTime] +"' and '"+(DT_STR,50,1252)GETDATE()+"'"
Thanks,
Phil
For example..
select * from mytable where MyNum = 7
If this brings back more than 1 row, I want to display a message that says,
Print 'There is more than one row returned'
Else (If only 1 row returned), I don't want to print anything.
Can I do this? Thx!
Dear friends,
I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...
I need to filter rows in a dataflow...
I created a condition spli to that... maybe there is a better solution...
And the condition is: Datex != NULL(DT_DATE)
(Some DATE != NULL)
[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.
What is wrong??
Regards,
Pedro
I am trying to write an visibility function to have message shown based on two different IIF conditions:
If behavior is to Add a customer ( if message =NAME ALREADY EXISTS, return " NAME ALREADY EXISTS",   otherwize return " NAME CREATED")If behavior is to Delete a customer (( if message =NAME DOES NOT EXIST, return "NAME DOES NOT EXIST",   otherwize return "NAME SUCCESSFULLY DELETED")
I tried the following which doesn't work:
=IIF((UCase(First(Fields!Message.Value, "DataSetName")) = "NAME ALREADY EXISTS"), "WARNING: NAME ALREADY EXIST", "NAMEÂ CREATED"),
IIF((UCase(First(Fields!Message.Value, "DataSetName")) = " NAME DOES NOT EXIST"), "WARNING: NAME DOES NOT EXIST", " NAME DELETED")