OUTPUT @count =no Of Records Returnd By Dynamic Where Clause
Aug 18, 2005
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."
View 1 Replies
ADVERTISEMENT
Jun 12, 2006
Hi,
I add new records to a table with ADO. The tables contain an auto-increment identity column. I want to retrieve the identity value after the insert operation. This works fine for SQL Server 2000. On SQL Server 2005 this only works if I use a table in the select statement. If I use a view in the select statement, ADO returns no value for the identity column, a trace with profiler shows that there is no Select @@IDENTITY statement.
What is the reason for this behavior?
How can I change this behavior in SQL 2005 so that the behavior is the same as in SQL Server 2000?
Best regards,
George
View 21 Replies
View Related
Mar 2, 2014
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
View 3 Replies
View Related
Nov 7, 2007
Hi,
I'm copying records from table A to Table B. On "insert" being successful I want to delete the copied records from table A. I want to use one single transaction. Could I use OUTPUT to delete all records in table A which key matches the ones on "inserted" ?. I believe it could be possible but I have tried several ways without any luck.
INSERT INTO Table A (Field 1, Field 2)
OUTPUT (DELETE Table B WHERE Key = (SELECT Key FROM Inserted))
SELECT (Field1, Field 2) FROM TABLE B
Or something like that,
Thank you
View 1 Replies
View Related
Aug 26, 2013
I have a query that is counting the number of applications gotten for the day.
SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], stm.UserID,
COUNT (MerApp.ApplicationID)AS [Number of Apps]
FROM MerchantApplication MerApp
INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID
INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID
INNER JOIN SalesTeams satm ON stm.SalesteamID = satm.SalesTeamID
WHERE MerApp.LastUpdateOn BETWEEN @strStartofDays AND @strEndDate AND MerApp.ApplicationStatus=3 AND STM.ActiveStatus=1
GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, stm.UserID, stm.SalesteamID, MerApp.ApplicationStatus, satm.TeamDescription
LastUpdateOn is the days the table is updated
Since it's just a 24hr count I should have to use BETWEEN in my where clause. What is another way to count for 1 day.
View 5 Replies
View Related
Feb 29, 2008
I would like to add more columns on output clause while insert....
Following are the table formats used here ...
Table1 (col1, col2)
Table2 (col1, col2, col3, col4)
MyTable (C1,C2,C3,C4,C5,C6)
Insert into Table1 (col1, col2)
Output Inserted.col1, inserted.col2,MyTable.C3, MyTable.C4 into Table2
select C1,C2 from MyTable
Now the Output statement retrives only the Inserted.col1, inserted.col2 values, But I want to add MyTable.C3, MyTable.C4 values from the table <MyTable>
Is there any way to achieve this....
Thanks...
View 1 Replies
View Related
Nov 14, 2006
In the past when inserting a record into a table with an identity column, to return the new identity value I used one of SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY.
Question: will this sql 2005 approach also provide the newly added identity value?
insert into TestTable -- ID column of testtable is an Identity field
output inserted.id, inserted.col1 into @insertedRecords
values('row 20')
select ID from @insertedRecords
TIA,
Barkingdog
.
View 3 Replies
View Related
Feb 29, 2008
I would like to add more columns on output clause while insert....
Following are the table formats used here ...
Table1 (col1, col2)
Table2 (col1, col2, col3, col4)
MyTable (C1,C2,C3,C4,C5,C6)
Insert into Table1 (col1, col2)
Output Inserted.col1, inserted.col2,MyTable.C3, MyTable.C4 into Table2
select C1,C2 from MyTable
Now the Output statement retrives only the Inserted.col1, inserted.col2 values, But I want to add MyTable.C3, MyTable.C4 values from the table <MyTable>
Is there any way to achieve this....
Thanks...
View 3 Replies
View Related
Apr 18, 2008
I am attempting to do something like this...
select * from applebucket ab where 2 = (select count(id) from apples ap where ap.applebucket_id = ab.id)
SSCE 3.1 throws the following
There was an error parsing the query. [ Token line number = x,Token line offset = y,Token in error = SELECT ]
Any help would be greatly appreciated!
View 3 Replies
View Related
May 9, 2007
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.
View 3 Replies
View Related
Jul 19, 2007
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
thanks for any suggestions provided.
View 6 Replies
View Related
Nov 13, 2006
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.
View 5 Replies
View Related
Dec 6, 2007
Hi all,
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'')
View 20 Replies
View Related
Sep 18, 2007
Hello everyone,
I want to build a dynamic where clause which makes :
WHERE column1 = (@parameter1 if @parameter1 is not null) / (anything if @parameter1 is null)
Basically I do not know how to set column1 = ANYTHING
Best regards and thanks.
View 5 Replies
View Related
Aug 23, 2007
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 -
set @sql = N'declare @p1 nvarchar(10),
@p2 nvarchar(10);
set @p1 = ''' + @parsedValue1 + ''', @p2 = ''' + @parsedValue2 + '''; ' +
N'select col1 from table1 ' + @whereCondition
exec (@sql)
(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.
Any advice would be appreciated.
View 5 Replies
View Related
Jan 29, 2008
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%')
)
Can I do this using Sql (Sql Compact 3.0)?
thanks,
View 1 Replies
View Related
Mar 10, 2008
Hi
I'm using SQL Server 2000...
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.
declare @StartDt datetime;
declare @EndDt datetime;
declare @WhereDate varchar(30);
if @report = 1 then
begin
set @WhereDate = 'Table.EndDate'
end
else set @WhereDate = 'Table.StartDate'
select *
from MyTable
Where @WhereDate >= @StartDt and @WhereDate < @EndDt
I'm getting syntax errors, so my question is.... is it possible... if so what syntax should I use?
Cheers
Jon
View 8 Replies
View Related
Dec 18, 2006
I need to update a table using a stored procedure that "UPSERTS". In this case, @OfficeId is passed with ParameterDirection.OutputSo when I update I assign the value to OfficeId like this (the office id value is being correctly assigned): SqlParameter OfficeIdParam = new SqlParameter("@OfficeId", SqlDbType.Int, 4); if (_officeId > 0) { OfficeIdParam.Value = _officeId; } OfficeIdParam.Direction = ParameterDirection.Output; The following segment of the stored procedure is throwing an error that the OfficeId does not exist: Update --- @OfficeId int output if not exists (select 1 from ORG_Office where OfficeId=@OfficeId) begin RAISERROR ('OfficeId does not exist in ORG_Office: E002', 16, 1) -- OfficeId does not exist. return -1 end May this approach work, and if the operation turns out to be an Insert I may do this:select @error_code = @@ERROR, @OfficeId= scope_identity() Thank you,jspurlin
View 1 Replies
View Related
Sep 26, 2014
I need to update the account_status and show the audit in the ssrs report.
I wrote the following query in the ssrs report but when I click on refresh fields it is throwing error incorrect syntax near output
Declare @username nvarchar(50)
Declare @UserStatus Table
(id bigint,
Username nvarchar(50),
Oldaccount_status varchar(255),
Newaccount_status varchar(255))
[Code] .....
View 2 Replies
View Related
May 28, 2015
I am trying to update a table and then also use OUTPUT clause to capture some of the columns. The code that I am using is something like the one below
UPDATE s
SET Exception_Ind = 1
OUTPUT s.Master_Id, s.TCK_NR
INTO #temp2
FROM Master_Summary s
INNER JOIN Exception d
ON d.Id = LEFT(s.Id, 8)
AND d.Barcode_Num = s.TCK_NR
WHERE s.Exception_Ind IS NULL
The above code is throwing an error as follows:
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.Master_Id" could not be bound.
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.TCK_NR" could not be bound.
View 4 Replies
View Related
Jun 5, 2008
I have the following query where I select records from Active_Activities_temp which do not match on cde_actv in the table ACTIVITY_CORE_LISTING:
SELECT Active_Activities_temp.*
FROM Active_Activities_temp LEFT JOIN
ACTIVITY_CORE_LISTING ON
Active_Activities_temp.cde_actv=ACTIVITY_CORE_LISTING.cde_actv
WHEREACTIVITY_CORE_LISTING.cde_actv is null
ORDER BY prtcpnt_id
So for example, if a participant has a cde_actv=38 (which doesn't exist in ACTIVITY_CORE_LISTING), that record would appear as the query is currently.
The issue is that participants can have multiple records in Active_Activities_temp and if a participant has a record that does exist in ACTIVITY_CORE_LISTING, no records for that participant should appear in this query result. For example, if a participant has two records in Active_Activities_temp, one with a cde_actv 38 (which does not appear in ACTIVITY_CORE_LISTING) and one with a cde_actv 33 (which does appear in ACTIVITY_CORE_LISTING), no records for that participant should appear in the result. Currently the record with cde_actv=38 does appear.
What code can I implement to do what I need to do? Thanks so much.
View 5 Replies
View Related
Aug 7, 2015
I have an autogenerated MDX.
Select
Non
Empty{[Measures].[Exposure
Amount]} On
Columns,
Non Empty TopCount(Â
(Â AddCalculatedMembers(
[Code] ....
As a result I only get 29 records. One record in the middle is missing. If I change to Topcount 31 I get 32 records, where the missing record is also included.
If I remove the Zero supression I receive 30 records as expected. For me the MDX looks good. I will try to reproduce on AW.
View 5 Replies
View Related
May 21, 2007
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?
View 8 Replies
View Related
Apr 14, 2008
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')
Thanks for your help
View 2 Replies
View Related
Jun 26, 2007
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�.
Thank you
View 1 Replies
View Related
May 6, 2008
Hi,
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
mytable
id product
1 Monitor
2 Mouse
3 Keyboard
4 Modem
5 Monitor
My question is; how can i use CASE function dynamically ?? Mouse should get from lkpProduct....I do not want to write long table manually..
I hope it is clear for you..
Regards.
View 4 Replies
View Related
Apr 14, 2008
Hello,
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.
Thank you
View 8 Replies
View Related
Nov 16, 2007
Hi,
I am trying to build a parameterized query where I pass a set of integer values into the dynamic sql. Please see below example.
DECLARE @SQLQUERY NVARCHAR(4000)
DECLARE @PARAMDEF NVARCHAR(1000)
DECLARE @VALUES VARCHAR(100)
SET @PARAMDEF = N'@IN_VAL VARCHAR(100)'
SET @VALUES = '1,2,3,4'
SET @SQLQUERY = 'SELECT * FROM TABLEA WHERE COLUMNA IN (@IN_VAL)'
EXEC SP_EXECUTESQL @SQLQUERY,@PARAMDEF,@IN_VAL=@VALUES
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.
TIA
View 7 Replies
View Related
Jun 13, 2006
Hello all...
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??
i.e
where if a = 1 then d=e
else contains(.....)
thanks
View 9 Replies
View Related
Oct 16, 2014
How SQL calculates the estimated row count when < or > are used in the WHERE clause of a statement. For example:-
WHERE Created_Datetime_utc > CONVERT(DATETIME,'2014-10-14 10:00:00',102)
I know how the estimated number of rows are calculated when an = is used but I've been googling and cannot find anything about < and >.
View 2 Replies
View Related
May 25, 2004
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 2 Replies
View Related
Jul 23, 2004
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 1 Replies
View Related
Mar 2, 2006
Greetings,
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.
Any help is very much appreciated.
- Jeff
View 3 Replies
View Related
