Hi All,
How do I filter an EXEC table as to put the returned value into a variable?
I have to filter an EXEC table because I am using a table variable to define which table tto select.
Help appreciated.
Hello,
Here is my problem:
I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example:
create table A (ID int identity, NAME varchar(100), COMPANYID int)create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)
Also there are nearly 200 stored procedures that read data from these tables. Example:
create procedure ABCasbegin /* some checks and expressions here ... */ select ... from A inner join B on (A.ID = B.REF_ID) where ... /* ... */end;
All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.
However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.
Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.
However, I don't know what is the easiest and fastest way to filter the tables. Example:
I modified the above mentioned procedure in the following way:
create procedure ABCasbegin /* some checks and expressions here ... */
-- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))
select ... from A inner join B on (A.ID = B.REF_ID) where ...
and A.COMPANYID = @CompanyID and B.COMPANYID = @CompanyID /* ... */end;
Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables.
For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".
I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.
I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?
Thanks in advance.
Best regards,
Beroetz
I have the following query in sql 2005:
PROCEDURE [dbo].[uspInsert_Blob] (
@fName varchar(60),
@fType char(5),
@fID numeric(18, 0),
@bID char(3),
@fPath nvarchar(60)
)
as
DECLARE @QUERY VARCHAR(2000)
SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document)
SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB)
AS Document"
EXEC (@QUERY)
This puts some values including a pdf or .doc file into a table, tblDocTable.
Is it possible to change this so that I can get the values from a table rather than as parameters. The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport.
tblImportExport has the path for the document (DocPath) so I would subsitute that field, ie. DocPath, for the @fPath variable.
Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport.
Any ideas how to do this?
I want to pass the results of a stored proc into a table variable in SQL SEVER 2000. Something like this
declare @a table
(
employeeid int
)exec @a=dbo.MetricsProcessor_GetTopEmployees @parameter1, @parameter2
How can i do this? This is throwing an error.
Thanks.
Hi is there a way to store results of the query in a temp table?
Something like
select exec(sp_Run)
into #t1
Is this possible?
I need to create a dynamic temporary table in a SP. Basically, I am using the temp table to mimic a crosstab query result. So, in my SP, I have this:--------------------------------------------------------------------------------------- Get all SubquestionIDs for this concept-------------------------------------------------------------------------------------DECLARE curStudySubquestions CURSOR LOCAL STATIC READ_ONLY FOR SELECT QGDM.SubquestionID, QGDM.ShortName, QGDM.PosRespValuesFROM RotationMaster AS RM INNER JOIN RotationDetailMaster AS RDM ON RM.Rotation = RDM.Rotation INNER JOIN QuestionGroupMaster AS QGM ON RDM.QuestionGroupNumber = QGM.QuestionGroupNumber INNER JOIN QuestionGroupDetailMaster AS QGDM ON QGM.QuestionGroupNumber = QGDM.QuestionGroupNumberWHERE RM.Study = @StudyGROUP BY QGDM.SubquestionID, QGDM.ShortName, QGDM.PosRespValuesHAVING QGDM.SubquestionID <> 0--------------------------------------------------------------------------------------- Dynamically create a Temp Table to store the data, simulating a pivot table-------------------------------------------------------------------------------------SET @Count = 2SET @SQL = 'CREATE TABLE #AllSubquestions (Col1 VARCHAR(100)'OPEN curStudySubquestionsFETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValuesWHILE @@FETCH_STATUS = 0BEGIN SET @SQL = @SQL + ', Col' + CAST(@Count AS VARCHAR(5)) + ' VARCHAR(10)' SET @Count = @Count + 1 FETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValues ENDSET @SQL = @SQL + ', ShowOrder SMALLINT)'CLOSE curStudySubquestionsPRINT 'Create Table SQL:'PRINT @SQLEXEC (@SQL)SET @ErrNum = @@ERROR IF (@ErrNum <> 0) BEGIN PRINT 'ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!' RETURN ENDPRINT '*** Table Created ***'-- Test that the table was createdSELECT *, 'TEST' AS AnyField FROM #AllSubquestions The line PRINT @SQL produces this output in Query Analyzer (I added the line breaks for forum formatting):CREATE TABLE #AllSubquestions (Col1 VARCHAR(100), Col2 VARCHAR(10), Col3 VARCHAR(10), Col4 VARCHAR(10), Col5 VARCHAR(10), Col6 VARCHAR(10), Col7 VARCHAR(10), ShowOrder SMALLINT) However, the SELECT statement to test the creation of the table produces this error:*** Table Created ***Server: Msg 208, Level 16, State 1, Procedure sp_SLIDE_CONCEPT_AllSubquestions, Line 73Invalid object name '#AllSubquestions'. It appears that the statement to create the table works, but once I try to access it, it doesn't recognize its existance. Any ideas?
View 4 Replies View RelatedHi,
If i have an SP called mySP that accepts one parameter @param
If I have a table of paramaters with only one column like this:
Param1
Param2
..
ParamN
How do I do if I want to execute the SP on all the table fields:
some thing like this:
Exec my SP 'Param1'
Exec mySP 'Param2'
...
Exec mySP 'ParamN'
I want that automatically since the parameters are going to be in a table called myTblParams
Notice that I don t want to pass all the parameters to the SP just once but only one value each time I execute the SP since mySP ccepts only one parameter.
Thanks a lot for guidelines
I like to use the table "Inserted" within exec(), but it doesn't work because the scope is different. Does anyone have some sort of solution to this problem? The reason I am doing it this way is because I have a table consist of 200+ columns of bit types that contains permission information (The worest design i have ever seen!).
Code Snippet
--gather column names
declare @ScreenPermissions nvarchar(256)
declare c_Permission cursor
for
SELECT [name]
FROM syscolumns
WHERE id = (
SELECT id FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'ScreenPermissions'
)
and [name] like 'Allow%'
open c_Permission
fetch next from c_Permission
into @ScreenPermissions
while @@fetch_status = 0
begin
exec('INSERT INTO EmployeeInRoles (EmployeeID, RoleID) ' +
'select i.EmployeeID, r.RoleID ' +
'from inserted as i ' +
' inner join ScreenPermissions AS sp on sp.EmployeeID = i.EmployeeID and sp.' + @ScreenPermissions + ' = 1 ' +
' inner join Roles AS r on r.LoweredRoleName = Lower(' + '''' + @ScreenPermissions + '''' + ')' )
fetch next from c_Permission
into @ScreenPermissions
end
close c_Permission
DEALLOCATE c_Permission
How do we do this in SS2k5?
in
EXEC(sqlstring)
sqlstring wants to pass back a resultset to the caller.
- Local temp tables are out of scope.
- Global temp table works but is a bad idea.
- Table variables not supported as OUTPUT parameters for EXEC.
Regards, Nick
Hi,
I'm trying to capture the value returned from sprocs. I stored the sproc name in the table and use cursor to run each sproc. Now the question is how can I capture and store the return value in a variable?
Here is the scenario:
Table1 has 1 column varchar(50) called vchsprocname
count_A -- procedure, select count(*) from ...
count_B -- procedure, select count(*) from ...
count_C -- procedure, select count(*) from ...
here is my query:
----------------------------------------------------
DECLARE @vchsprocname varchar(50)
DECLARE @count int
DECLARE cur CURSOR FOR
SELECT vchsprocname from table1
OPEN cur
FETCH NEXT FROM cur
into @vchsprocname
WHILE @@FETCH_STATUS = 0
BEGIN
exec @count = @vchsprocname -- I know I cannot do this, the vchsprocname cannot be variable. What else can I do?
FETCH NEXT FROM cur
into @vchsprocname
END
--------------------------------------------------
Hello,
Here is my problem:
I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example:
create table A (ID int identity, NAME varchar(100), COMPANYID int)
create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)
Also there are nearly 200 stored procedures that read data from these tables. Example:
create procedure ABC
as
begin
/*
some checks and expressions here
...
*/
select ...
from A inner join
B on (A.ID = B.REF_ID)
where ...
/* ... */
end;
All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.
However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.
Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.
However, I don't know what is the easiest and fastest way to filter the tables. Example:
I modified the above mentioned procedure in the following way:
create procedure ABC
as
begin
/*
some checks and expressions here
...
*/
-- gets the CompanyID from the context:
DECLARE @CompanyID int;
SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))
select ...
from A inner join
B on (A.ID = B.REF_ID)
where ...
and A.COMPANYID = @CompanyID
and B.COMPANYID = @CompanyID
/* ... */
end;
Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables.
For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".
I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.
I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?
Thanks in advance.
Best regards,
Beroetz
I have several reports where I am trying to use a single sp, and filter some of the paramater selections in reporting services.
I can get a single item to work in the filters
ie: state.value = SC
but i have not been able to get a multivalue scenario to work.
ie: State.value in SC,GA,TN
or State.value in ('SC','GA','TN')
or State.value in ("SC","GA","TN")
or State.value in 'SC','GA','TN'
or State.value in "SC","GA","TN"
I am guessing it is a simple syntax thing, but well obviously I havent found it.
Any help would be appreciated
Rick
I'd like to set the Filters in the Filters tab of the Table Properties dialog to say:
=Fields!WT_TO.Value > 0 OR
=Fields!WT_TO_PREV.Value > 0
but teh And/Or column is permanently disabled, and its sticking in a default value of AND
what's up with that?
Some tables i have in a report. A couple of them work, but 2 others dont. I would do it in the query, but then i wouldnt be able to group them in a list.
So my question is, how come i can filter out my BillcustomerCode, but when i want to filter out my subbrand_id (which is an int) or subbran_ Descrition(which is a varchar). how would i filter out these types in the table filter.
Also, how would i do an "Or" instead of an "and" in my filter too.
I have one table where i need to have 3 things not in the table:
Bill_Cus_code !=RNPROF
Bill_Cus_code !=RNPROC
Sub_Brand_Id ! =65 (or Sub_Brand_Discription != "Pro Club")
how do i do this in the expression. It seems to take out "RNPROF and RNPROC" but not the third one. Why is this?
I am using RS 2005. I am setting up a Model for use within Report Builder so our clients can write their own reports.
View 12 Replies View RelatedI am generating some dynamic sql which I would like to run and return the data from, however when I run EXEC(@TEMP_TABLE_STRING) it cannot return the table I think because it is out of scope.
Is there a way around this.
declare@Tablename VARCHAR(150) = 'TEMP_LOCATIONS'
declare@FilterClause VARCHAR(512)=NULL
declare@Classes VARCHAR(100)=NULL
declare@IsExcel BIT = 1
DECLARE @SQL Nvarchar(MAX) = ''
SET NOCOUNT ON;
[Code] ....
Hi
I've used a temporary table in the stored procedure
I've created it as DECLARE @Temp TABLE (id INT)
in select clause I've used this temp table through the joins..
But I've also used a varchar variable to store my criteria...
which I'm building in the stored procedure
so inorder to use it in the where clause I used
EXEC ('SELECT ....................
FROM @Temp T
LEFT OUTER JOIN ...
LEFT OUTER JOIN ...
WHERE ' + @Criteria )
Unfortunately this is not working.
Giving the errror
Must declare the variable '@TempT'.
I had to use Temporary table using #, which I feel is really waste of memory...
Is there a way by which I can use my Table variable in the EXEC statement.
Thanks In advance
Dear all,
My question about SQL statement:
Table1 - Fields: T1PK, T1Field
Records:
1, ABC
2, DEF
3, GHI
Table2 - Fields: T2PK, FK (join T1PK), T2Field
Records:
101, 1, XXX
102, 1, YYY
103, 1, XXX
104, 2, XXX
I want to write a SQL statement that join Table1 and Table2 together, T2Field equal to XXX, and list all records from Table1.
That's the result columns: T1PK, T1Field, T2PK, T2Field
Result records:
1, ABC, 101, XXX
1, ABC, 103, XXX
2, DEF, 104, XXX
3, GHI, NULL, NULL
Is it possible? Please help!!
Joey
I have a table that has multiple records as illustrated in the simple list below. The real list is much longer.
MachineA 1/1/2008
MachineA 1/3/2008
MachineB 1/7/2008
MachineB 1/8/2009
MachineB 5/5/2010
MachineA 5/7/2011
MachineA 4/2/2013
I need to query to return a result for each unique machine with the latest date. The example result below would be returned because they have the latest date.
MachineA 5/7/2011
MachineB 5/5/2010
Select Distinct would almost do it, but I need each unique machine that has the latest date.
Table filter operation where I want to filter against any combination from any of the available fields:
table a(f1 int, f2 int)
2 indexes - first by field f1, second by field f2
data - many records like (f1=1, f2=2), and many records like (f1=3, f2=4)
sql: select * from a where f1=1 and f2=4
result will be - no records, but full table scan is required, indexes are not used
question - how to optimize above structure, but do not create compound index by (f1, f2)?
if we will have many fields (lets say 10), which can be used in filter in any combination we will need to create 10! Indexes. Any way to avoid or optimize it for a high volume searches?
Hai guys,
In my report I want to use OR condition instead of AND in Table Filter Expression. By default this option is disabled in VS2005 Pro. How do I enable this? or is there any other way to do this? Thanks in advance.
We have a fact table where one of the measures in that fact table is invoice balance. Some of our reports requirements list that they only want to see the sum of those invoices where the balance is greater than X dollars. How do I go about filtering calculations based on a measures value?
View 3 Replies View RelatedHello,Is it possible to EXEC stored procedure from a query?I want to execute stored procedure for every line of SELECT resulttable.I guess it's possible with cursors, but maybe it's possible to make iteasier.Give an example, please.Thank you in advance.Hubert
View 2 Replies View RelatedHi all.
First, sorry about my poor english.
I have a database which above 6 gb data and i have droped all table in my database.
I try to restore from log file but my database is set to simple backup and auto shrink so log file doesn't help anymore.
I have used some software to recovery from log file too but useless.
My only hope right now is mdf file.
Please help me. How could i restore my mdf file to state before i droped tables.
Thanks
I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:
Insert into #temp
Exec(@sqlcommand)
For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.
I have a big table with several types of transsactions: PO (Puchase Orders, SO,( Sales orders), INV (Invoices) ....
I want to create cubes with only one type of transactions (1 cube for PO,...)
Where and how can I filter the rows I want to use in my cube ?
Thanks
i am having 3000 records in table. now i want take out the duplicate from that table.
for example: i want to find out the duplicates of 'CompanyNames'.
help needed to write query for this operation.
H, need help please!
I have two tables on a reports, each having it's own dataset.
I want to set the visibility of the table depending on the filter selection.
So if the user selects: show open then show only the table with the open dataset.
I don't now how to this using the expression in the visibility expression.
Please Assist!
Regards
I am using VS 2005/SQL 2005 and must use a stored procedure as provided. The stored procedure returns parent rows and child rows (same as parent with additional columns populated, like ChildID). I cannot change the stored procedure.
I have been unable to filter the data (either at the Dataset level or the Table Properties level) to only see the "parent" rows. On my report, I added one column to print the value for my test and a second to use it:
=Trim(CStr(Len(Fields!ChiIdID.Value)))
=IIf(Trim(CStr(Len(Fields!ChildId.Value)) = "0"),"PARENT","CHILD")
Both columns produce the values I expect. However, if I try a filter using the first expression, it fails! I.E. if I test = 0, it filters out ALL rows and if I test != 0, all rows are displayed?????
I am new to SSRS, so I would not be surprised if this is cockpit error. It seemed like a very simple requirement that I have used in othe report tools for years.
Any help would be appreciated.
Howdy,
I have a table that has a group. In this group, I want to filter by 2 different expressions, concatenated with an OR. BUT I can't change the "And/Or" column value for the first entry because it is grayed out. The column will automatically change to an OR value if both my expression column fields are the same (which I don€™t want) but if I put any other value in to the expression field of the second row, the "And/Or" field of the first row automatically changes to an AND.
PLEASE! How do I get the And/Or field "ungrayed" so I can change it to what I want?
The 2 filters I and using check the UserID = to the user, and the other is checking a count to get the Top N 1. (So just showing the current user and the top producer)
I have a temporary table with multiple records and a Stored Procedure requiring a value. In a Stored Procedure, I want to loop through records in the table and use the value from each record read as input to another Stored Procedure. How do I do this?
View 7 Replies View RelatedHello,
Can anyone shed some light on why the following:
declare @str varchar(2000)
set @str = 'SELECT * INTO #TmpTable FROM FormHistory'
exec (@str)
SELECT * FROM #TmpTable
gives the following error:
Invalid object name '#TmpTable'.
This is a very cutdown version of what I am trying to achieve so it might not seem obvious why I am writing it into a string and using exec but in the real code I do need to do this. I have cut it right back to try to get to the bottom of why this doesn't work. I suspect the # in the string is causing the problems.
Thanks for any help
I have a set of udf's dealing that return a one column table of valuesparsed from a comma delimeted string.For example:CREATE FUNCTION [dbo].[udf_filter_patient](@patient_list varchar(2000))RETURNS @patient TABLE(patient_id int)ASBEGINinsert into @patientselect patient_id from patient-- parse @patient_list stuff excludedRETURNENDI have come up with the following two schemes to use these udfs. Theseexamples are obviously simplified, and I have a handful of stored proceduresthat will use between 10 or more of these filters. If the two areequivalent, I prefer Method 2 because it makes for much neater SQL whenusing many filter criteria.So my question is, will one noticebly outperform the other? Or is there abetter way in which to filter on a list of criteria?Method 1 :CREATE PROC sp__filter_open_bills@patient_list varchar(2000)ASCREATE TABLE #patient(patient_id int)INSERT INTO #patientSELECTpatient_idFROMdbo.udf_filter_patient( @patient_list )SELECT*FROMopen_billsINNER JOIN #patient on #patient.patient_id = open_bills.patient_idGOMethod 2 :CREATE PROC sp__filter_open_bills@patient_list varchar(2000)ASSELECT*FROMopen_billsWHEREopen_bills.patient_id IN ( SELECT patient_id FROMdbo.udf_filter_patient( @patient_list ) )GOThanks for the help!Chris G
View 4 Replies View Related