Exec Multiple Batches (GO) With Dynamic Sql

Jun 10, 2008

Hello SQL Team!
I'm stuck at this problem for days and need help.
The problem is with the GO keyword. I know the GO causes the batch to get executed and all local variables are lost. But I can't seem to find a work around. I would like each stored procedure to get executed in different batches.

create table sql_cmd(cmd nvarchar(255))
insert into sql_cmd(cmd) values('exec user_sp param1,''param2'')
insert into sql_cmd(cmd) values('exec user_sp2 param1,''param2'')

declare @sql nvarchar(255)
declare c_sql cursor --small table peformance not a problem. Also open to other suggestions
for select cmd from sqlcmd
open c_sql
fetch next from c_sql
into @sql
while @@fetch_status = 0
begin
print @sql
exec sp_executesql @sql
GO
fetch next from c_sql
into @sql
end

View 4 Replies


ADVERTISEMENT

EXEC Dynamic Sql

Oct 17, 1999

How can i get the result from a dynamic sql like - exec('select ' + fieldName
+ ' from ' + TableName)

View 2 Replies View Related

EXEC Dynamic Sql

May 13, 2003

Running this dynamic sql construct gives me an error because somehow it does not accept my variable @table or it is recognised differently. If run directly no problem but apparently the single quotes are a problem.

Print @Table (db and table name: opms..transactions)
Select @sql = 'Select * From Payments where not exists (Select * from Hist Where TableName = ' + @Table + ' and sYear = '+ @Year + ' and sMonth = ' + @Month + ')'
Print @sql
EXEC (@sql)

opms..Transactions
Select * From Payments where not exists (Select * from Hist Where TableName = opms..Transactions and sYear = 2003 and sMonth = 12)

Server: Msg 1004, Level 15, State 1, Line 1
Invalid column prefix 'opms.': No table name specified

Any idea?

mipo

View 2 Replies View Related

Dynamic SQL And EXEC

Aug 11, 2005

Hello,

I'm trying to do something like the following, but it keeps complaining that I need to declare @max, even though I have (and it is of the same type as link_id).

EXEC('SELECT @max=MAX(link_id) FROM '+ @str1)

I've looked into sp_executesql but I'm not entirely sure how that functions. Any suggestions?

View 3 Replies View Related

Incorrect Syntax Near The Keyword 'EXEC' (Dynamic Sql Statement Error)

Nov 1, 2007

Following is the stored procedure iam trying to create.Here i am trying to

First create a table with the table name passed as parameter
Second I am executing a dynamic sql statement ("SELECT @sql= 'Select * from table") that returns some rows.
Third I want to save the rows returned by the dynamic sql statement ("SELECT @sql= 'Select * from table") in the tablei created above.All the columns and datatypes are matching.

This table would be further used with cursor.
Now i am getting a syntax error on the last line.Though i doubt whether the last 3 lines will execute properly.Infact how to execute a sp_executesql procedure in another dynamic sql statement.ANy suggestions will be appreciated.


CREATE PROCEDURE [dbo].[sp_try]

@TempTable varchar(25)


AS

DECLARE @SQL nvarchar(MAX)

DECLARE @SQLINSERT nvarchar(MAX)



BEGIN


--create temp table

SELECT @Sql= N'CREATE TABLE ' + QUOTENAME(@TempTable) +

'(

ContactName varchar (40) NOT NULL ,

ContactId varchar (30) NOT NULL ,

ContactrMessage varchar (100) NOT NULL,



)'

EXEC sp_executesql @Sql, N'@TempTable varchar(25)', @TempTable = @TempTable


SELECT @sql= 'Select * from table'




SELECT @sqlinsert = 'INSERT INTO ' + quotename( @TempTable )

SELECT @sqlinsert = @sqlinsert + EXEC sp_executesql @sql, N'@Condition varchar(max)', @Condition=@Condition

EXEC sp_executesql @SQLINSERT, N'@TempTable varchar(25)', @TempTable = @TempTable

View 8 Replies View Related

Multiple Concurrent Sql Exec Commands?

Mar 12, 2008

I was wondering if it was possible to run 2 queries at the same time,IE rather than runningexec query1  exec query2 and having to wait for query1 to finish before query2 started, just have them run at the same time (they're not conflicting).I want to do this in TSQL, not by opening another query window in vs2005. 

View 2 Replies View Related

Exec Storeprocedure - Multiple Joins

May 5, 2008

Hi,I am written a store procedure that would access four tables and grab appropriate fields.. I am using JOIN functionality because it can have multiple rows. ( The goal is: )Step 1: User can search by ID  or MEMBER_ID or both .. grab all the data from mainTable based on the search. WORKS.Step 2: TABLE 2 (userTable table) get EMAIL for each record that was grabbed.. based on the ID. WORKS.Step 3: TABLE 3 and TABLE 4.. I am having some problems combing into the query.. how to add multiple JOINS… Is it safe? Please see below what data needs to be combined into the query.--Code works for Step 1 and 2.declare @ID varchar(20), @MEMBER_ID varchar(20) set @ID= null --testing data.. set @MEMBER_ID ='15552' –testing data.. Select MainTable.REFNO,MainTable.ID,mainTable.MEMBER_ID,userTable.EMAILFROM mainTableLEFT JOIN userTableON mainTable.ID = userTable.IDWhere (mainTable.ID = @ID  OR @ID IS NULL) and(mainTable.MEMBER_ID = @MEMBER_ID OR @MEMBER_ID IS NULL)TABLE 3:  (works by itself)SELECT  SR.COMPANY, SR.LOCATION_NOFROM    SI INNER JOIN  SR ON      SI.SR_ID = SR.SR_IDWHERE   SI.ID = MainTable.ID)ORDER BY SR.DATE_RECEIVED DESCTABLE 4:  (works by itself)I will be retrieving LOCATION_NO from SR table and comparing the value to the below query: for each record that was found in  the mainTable.select LOCATION_NAME from locationwhere LOCATION_NO= SR.LOCATION_NO       Please help me solve this.. Thank you  

View 5 Replies View Related

Join Results From Multiple EXEC Calls

Apr 25, 2006

I would like to make inner join with results from different exec %procedureName% calls.

thanks.

View 3 Replies View Related

Sql Batches In Vs.net

Jul 20, 2005

visual studio.net seems to default to single batch mode when runningsql scripts. does anyone know how to change this behavior? typicalbatches will include object existence, drop, and create batches priorto processing. i have attempted removing the graphical plan, usingbatch separator 'go', and the vba trick using ';' to no avail.tia

View 1 Replies View Related

Help Understanding Batches

Dec 4, 2007

I am using SQL Server Express and Visual Studio 2005. I am new to batches and am trying to understand how they work. I am trying to write a query that creates an assembly and the functions that are contained in it. Here is my query:

USE ProductsDRM
GO

IF NOT EXISTS (SELECT 'True' FROM sys.assemblies WHERE name = 'ComputedColumnFunctions')
BEGIN
CREATE ASSEMBLY ComputedColumnFunctions
FROM 'C:WebsitesAssemblyTestStoredFunctionsStoredFunctionsinStoredFunctions.dll'
GO

CREATE FUNCTION fImageFileName
(
@ProductID int,
@ImageSizeCode nvarchar(4000)
)
RETURNS nvarchar(4000)
AS EXTERNAL NAME [ComputedColumnFunctions].[StoredFunctions.UserDefinedFunctions].ImageFileName
GO

CREATE FUNCTION fTestInt
(
@ProductID int
)
RETURNS int
AS EXTERNAL NAME [ComputedColumnFunctions].[StoredFunctions.UserDefinedFunctions].TestInt
GO

CREATE FUNCTION fTestInt2
(
@TestInt int
)
RETURNS int
AS EXTERNAL NAME [ComputedColumnFunctions].[StoredFunctions.UserDefinedFunctions].TestInt2
END
ELSE
BEGIN
PRINT 'The assembly named "ComputedColumnFunctions" already exists. No new assembly was created.'
END

GO

I read in a book about SQL Server 2005 about including a test for whether the object (such as assembly in this case) exists before trying to create it. If I only include the CREATE ASSEMBLY statement and the FROM line below it and delete the next GO down through the last CREATE FUNCTION (just before the END ELSE), it works fine. If I leave it as is, I get a runtime error on the GO line just after the CREATE ASSEMBLY statement. What am I doing wrong?

View 5 Replies View Related

How Do I Update A Table In Batches ?

Jul 25, 2006

I have an application that processes a large number of input files in a CSV format and then posts the data to a table on SQL Server Express.

The data table can end up very large and I have no requirements to store all the data locally.
I have included the table in my DataSet using visual studio express so I have access to the schema, but will not run Fill() on it.

Ideally I would like to process a CSV file at a time.
I can add records to my local (empty) data table and when I am happy, I can call tableAdapter.Update() or dataSet.DataTable.AcceptChanges() to generate lots of SQL 'INSERT' commands to update the physical database at the server end.

I would then like to empty my local data table (so it doesn't get too big) and repeat the same process over again for each CSV file.

How can I empty my local table without causing it to generate a load of SQL 'DELETE' commands? I want to empty the table and fool ADO.NET into thinking that everything is synchronised, as if it has just done an update but actually hasn't.


Regards

View 3 Replies View Related

T-SQL (SS2K8) :: Updating Rows In Batches?

Nov 6, 2014

I have a production table with 400 million rows.

I have a staging table which has 48 million rows. This data is the same as the production data, except one column has a different value.

Create Table Production
(
Id Int Identity(1,1),
Code Varchar(20),
ReferenceSequence int
)

-- Staging Table

Create Table Staging
(
Code Varchar(20),
NewSequence int
)

I need to update the production table with the newSequence value from staging to replace the ReferenceSequence. I.e:

Update Production

Set ReferenceSequence = Staging.NewSequence
From Staging

where Production.Code = Staging.CodeHowever, updating 48 million rows at once will generate a lot of logging!

How can I do 1 million rows at a time, commit the changes then do the next million?

I've tried some of the examples on the following page [URL], but they look to just update the tables with the same values.

View 4 Replies View Related

What Sorts Of Statements Have To Be In Seperate Batches ?

Apr 7, 2008

Recently I was stumped on a problem where I was granting permissions to a user, from within a script that was creating a stored procedure. Then I would check the permissions on the procedure, but the permissions were empty. It turned out that I was missing a "go" statement to seperate the create procedure statement from the grant statement. So that got me to thinking about what other kids of statements must be seperated into seperate batches ? I would thik that anything that is being created must be seperated from any statements that grant or alter permissions, because the items would need to exist first.

comments ?

View 2 Replies View Related

DB Engine :: Deadlock While Updating In Batches

Oct 26, 2015

So i tweaked a stored procedure that did a 1 hour update for a specific countryId.

If that procedure was called at the same time with two different countryId than one update took place and afterwards the other.

Since all the rows are distinct i switched to an batch update only updating 10000 rows at a time ( and not all of the 2 million).

The general locking looked better afterwards but now i receive strange deadlocks.

My theory:

TX1 Updates a row on Page1 (P1) with rowlock. TX2 also does this on P1. Now TX1 deceides to escalate to PAGELOCK. TX1 waits for TX2 to be done with the row so it can lock the page. TX2 waits for TX1 to leave the page since TX2 may also want to pagelock.

Everybody waits for each other so we have a deadlock. Is that feasible ? OR is there another common problem when doing batch updates on the same table with distinct rows ( that can be on a same datapage ofc).

View 5 Replies View Related

Wrapping Query Batches In A Single Transaction.

Jul 23, 2005

I have a query batch "update" script that upgrades my users database from,say version 0 to version 1, or from version 1 to version 2. I would like toknow how I can wrap the entire script in a transaction, so that either thewhole thing succeeds or none of it does.For example:BEGIN TRANSACTION.......... Alter some tables.....GO.......... Alter a stored procedure.....GO.......... Create a new stored procedure.....GOCOMMIT TRANSACTIONorROLLBACK TRANSACTIONGO(how do I get to the "ROLLBACK TRANSACTION" if an error occurs in the updatescript?)

View 2 Replies View Related

Multiple Dynamic Inserts With SQL

Sep 10, 2007

 I'm try to a multiple insert from one database to another by using this code:insert into [mpis].[dbo].[Residents] (acno,surname,name,ID,type)        (select top 30 acno,surname,name,id,type        from [PretoriaDB].[dbo].[WorkingDB])  but I keep on getting this error:Msg 8152, Level 16, State 9, Line 1String or binary data would be truncated.The statement has been terminated.  Can any one help!! 

View 7 Replies View Related

Can Any One Tell Me How To Delete Data In Batches Of 10000 From A VLDB Table

Nov 14, 2001

Hi,
I would like to delete a data from a 750million row table in chunks of 10000,without blocking the users.As ours is a 24/7 shop I donot want to block the users for a long time.
Answer for this is highly appreciated.
Thanks
Samna

View 3 Replies View Related

SQL Server 2012 :: Updating 25 Million Records In Batches

Nov 10, 2014

I have 2 tables with this schema

CREATE TABLE tableValues(
[LASTENCRYPTIONDT] [datetime] NULL,
[ENCRYPTIONID] [int] NULL,
[NAME] [varchar](50) NULL

[Code] ....

I want to update tableToUpdate in batches of 5000 per batch and set the lastenecryptionDT to null based on the the join to the tableValues using the column ENCRYPTIONID, and also output updated rows into another table. Incase I would need to do a rollback.

View 3 Replies View Related

Ad-hoc Dynamic Query With Multiple Tables

Feb 8, 2005

Hi:
I'm trying to create an ad-hoc query on a Asp.net page for user. Besides the usual Boolean operators, Field Names, Comparison operators & Field Values, the ad-hoc query also involves multiple tables, eg [Customers] , [Members] & [Orders].

Now I have difficulties on writing a TSQL sp on how to take the dynamic query with different tables under consideration. User might simply query each individual tables (eg, Customers with age > 25) or combination of tables (eg, Membered Customers with Orders Amt > 1000 between 1/1/2005 - 1/31/2005)

I have look up a lot dynamic query on the net but all are with only 1 single table to hit. Could anyone give me a direction on how to write a dynamic query script with multiple tables under consideration? Much appreciated.

ps: The ad-hoc query only contains these defined tables, no other table will be involved.

View 3 Replies View Related

SQL Server 2014 :: Group Up Records Randomly Into N Number Of Batches

Jul 6, 2015

I need to group up the records randomly into ‘n’ number of batches. That can be done by NTILE, but I want group up similar records in single group.

Say for example, following is the list of records I have in my table which I want to group into 5 batches

A123
A124
A124
A123
A127

After Ntile I will get the below,

Desired output is, Need output like Ntile but all same id should reside in single batch

Even if I n=5, maximum possibility of batches are 3 only.

View 2 Replies View Related

Transact SQL :: GO Statements To Execute Large Stored Procedure In Batches

Jun 19, 2015

I want to include GO statements to execute a large stored procedure in batches, how can I do that?

View 9 Replies View Related

T-SQL (SS2K8) :: How To Add Multiple Spaces To Dynamic String

Jun 15, 2015

I am writing dynamic sql to create a work table. I would like to format my create script such that the data types are all lined up, rather than one space behind the column name. I tried something like this:

SELECT @SQL2 = COALESCE(@SQL2,'')
+ ' ,' + p.Attribute + (60 - LEN(P.Attribute)) * CHAR(32)+ 'NUMERIC(12,3)' + CHAR(10)

That is, I was trying to put the data type 60 spaces to the right, no matter how long the column name is (as long as it's less than 60 characters). I kept getting an error telling me it couldn't concatenate a character to an int.

View 2 Replies View Related

Check Multiple Parameter Instead Of Dynamic Query?

Oct 20, 2014

how to check multiple parameter instead of dynamic query.

View 1 Replies View Related

How To Load Dynamic Multiple Flat Files

Apr 23, 2008



Hi Evry one,

I Have Multiple Flat Files in Source Folder(They have Naming Conventions With Todays Date ex: Flatfile_20082204_1,Flatfile_20082204_2,Flatfile_20082204_3 ),
I need to Extract Each and Evry file by Dynamically, and Transform the Flat File then load that Flat file into the Destination Folder with Standard Prefix and Todays Date with a Sequence No ex:Flatfile_20082304_A,Flatfile_20082304_B, Flatfile_20082304_C


Please HELP Me
Thanks In Advance.

View 20 Replies View Related

Dynamic Pivot Table With Multiple Tables

May 19, 2015

How to pass dynamic values in xml path query?

WITH TEST AS (
SELECT TL.TERMINAL_ID,T.IP_ADDRESS, T.LOGICAL_CONNECT_STATUS, SI.SCHEDULER_ID,
SI.INSTRUCTION, SI.GROUP_ID, SI.MAX_READ_RETRIES, SI.DATA_CHAR, SI.SCHEDULE_TYPE,SI.FILEPATH_FLAG,
T.STATION_NAME,T.BANK_ID FROM SCHEDULERINFO SI  
INNER JOIN TERMINALGROUP TG  ON SI.GROUP_ID = TG.GROUP_ID INNER JOIN TERMINALGROUPLINK TL  ON TG.GROUP_ID = TL.GROUP_ID

[Code] ....

I need to pass dynamic values in FOR SCHEDULER_ID COLUMN. Because I have huge data.

View 7 Replies View Related

Problem With Dynamic Source For Multiple File

May 1, 2008

I have a problem while creating a dynamic source connection.

i have four files while which comes with different extension , apparently the name of the files are same for example.

9500.txt, the other one is 9500. rtf, 9500.dat, 9500.map, They are all text file but with different formats. I have problem selecting a dynamic source connection for each of this files.

when i create variable for input file name the file connection gets confused and throws me error with input file.

please help

Thank you




View 13 Replies View Related

SQL 2012 :: Deleting Large Batches Of Rows - Optimum Batch Size?

Oct 16, 2015

In another forum post, a poster was deleting large numbers of rows from a table in batches of 50,000.

In the bad old days ('80s - '90s), I used to have to delete rows in batches of 500, then 1000, then 5000, due to the size of the transaction rollback segments (yes - Oracle).

I always found that increasing the number of deleted rows in a single statement/transaction improved overall process speed - up to some magic point, at which some overhead in the system began slowing the deletes down, so that deleting a single batch of 10,000 rows took more than twice as much time as deleting two batches of 5,000 rows each.

good rule-of-thumb numbers (or even better, some actual statistics and/or explanations) as to how many records should be deleted in a single transaction/statement for optimum speed? 50,000 - 100,000 - 1,000,000 or unlimited? Are there significant differences between 2008, 2012, 2014?

View 9 Replies View Related

Transfer SQL Server Objects Task (for A Table): Can It Be Split Into Smaller Batches

May 29, 2008

We are using the Transfer SQL Server Objects Task to transfer a large table. The trans log is filling up for this table. Is there a method to split the Data Transfer Task into smaller batches? (Smaller tables are transferring without issue.)

Thanks.

View 2 Replies View Related

Transact SQL :: Cursor To Create Multiple Dynamic Procedures

Jun 29, 2015

The requirement is create a sql script(1 proc or cursor) which will create multiple procedures dynamically.

Table A
Col1 
Col2

A
Alpha

For Example: If table A has 3 rows(distinct) so 3 procedures will be created dynamically.

Result: 
1 PROC_A_ALPHA
2 PROC_B_BETA
3 PROC_C_charlie

View 6 Replies View Related

Analysis :: Tabular Dynamic Security With Multiple Values

Aug 6, 2015

It seems simple but I'm not able to make it works. I've got a fact table with sites (DimImplantation) with allowed users (DimDroit) :

1 user can have access to many sites. I've tried these codes but only one worked and only in case where the user had only one site :

a) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME() ) ==> worked for 1user with 1 site

b) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME(); DimDroit[DimImplantation_ID];DimImplantation[DimImplantation_ID] ) ==> doesn't work and says 'not able to to identifie the value of DimImplantation[DimImplantation_ID]
in the current context.

c) CALCULATE(VALUES(DimDroit[DimImplantation_ID], SUMMARIZE('DimDroit',DimDroit[Utilisateur]), DimDroit[Utilisateur]=USERNAME()) ==> version for  SQL2012. I've had 'End of input reached'.

View 3 Replies View Related

Dynamic Select Statement Using Parameterized SqlCommand Or Multiple Possible .CommandTexts

Jul 30, 2007

I'm trying to create an interface for filtering profiles from an SQLServer 2005 database using an html form. The form allows filtering based on a minimum level required in between one and four different columns. The first (and only mandatory) column to be filtered on has its name hard-coded into the base query. In trying to get the other three possible criteria to work, I've taken several approaches, all of which failed.The other three potential criteria are selected from a drop down menu on the form and ideally these choices are passed into a query to be used as column names. My first attempt looked like this:    query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)"    ....    if ReqSkill1 <> "" then                level1 = Convert.ToInt32(Request.form("minskilllvl1"))                query = query & pickclmleft & ReqSkill1 & pickclmright1                cmd.Parameters.Add("@ReqSkill1", SqlDBtype.text)                cmd.Parameters("@ReqSkill1").value = ReqSkill1                cmd.Parameters.Add("@level1", SqlDBtype.int)                cmd.Parameters("@level1").value = level1    end if   above If statement was repeated for 2nd and 3rd optionsSecond approach was to remove all parameters from sections of the query that were appended onto the original statement. This involved lots of strings containing AND clauses with hard-coded column names which were appended on when the corresponding option was selected in the form. Code looked like this:     query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)"     ASPqry = " AND (ASPlevel >= "    try             con = new SqlConnection()            con.ConnectionString = “**************string was correct****************â€?                        cmd = new SqlCommand()            cmd.Parameters.Add("@english", SqlDBtype.int)            cmd.Parameters("@english").value = english                                    if ReqSkill1 <> "" then                if ReqSkill1 = "ASPlevel" then                    query = query + " AND (ASPlevel >= "                    level1 = Convert.ToInt32(Request.form("minskilllvl1"))                    if level1 = 0 then                        query = query + "0)"                    end if                    if level1 = 1 then                        query = query + "1)"                    end if                    if level1 = 2 then                        query = query + "2)"                    end if                    if level1 = 3 then                        query = query + "3)"                    end if                end if            end ifFinally when this too failed, I created four entirely separate queries, detected how many criteria were used, and used the appropriate query, passing necessary skill level in as a parameter. I'll provide code if needed here. Queries were written as strings and then used to set the CommandText property for an SqlCommand variable. I think it's important to note that in all cases the most basic version of the query worked. In the first, if only the first criteria was used the statement executed fine. Same in the second. In the third, whatever query could be assigned first (even though only one could be assigned because of logical structure of if statements) worked and none of the others would. This last case was tested even with completely hard-coded queries that SQL Server 2005 validated as correct and would run. Any help is greatly appreciated. Will post as much code as people want/need, and if I can get any one of these methods working I'll be thrilled. I have no need for all three. A.S. Moser 

View 4 Replies View Related

SQL Server 2012 :: Dynamic Table Pivot With Multiple Columns

Jan 23, 2014

I am trying to pivot table DYNAMICALLY but couldn't get the desired result .

Here is the code to create a table

create table Report
(
deck char(3),
Jib_in float,
rev int,
rev_insight int,
jib_out float,

[Code] .....

Code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(deck)
FROM (SELECT p.deck FROM dbo.report AS p
GROUP BY p.deck) AS x;

[Code] ....

I need all the columns to be pivoted and show on the pivoted table. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!!

View 1 Replies View Related

1 SP With Dynamic Input Parameters And Multiple Rows As The Source Of The Query

Dec 4, 2005

How can I run a single SP by asking multiple sales question eitherby using the logical operator AND for all the questions; or usingthe logical operator OR for all the questions. So it's alwayseither AND or OR but never mixed together.We can use Northwind database for my question, it is very similarto the structure of the problem on the database I am working on.IF(SELECT OBJECT_ID('REPORT')) IS NOT NULLDROP TABLE REPORT_SELECTIONGOCREATE TABLE REPORT_SELECTION(AUTOID INT IDENTITY(1, 1) NOT NULL,REPSELNO INT NOT NULL, -- Idenitifies which report query this-- "sales question" is part ofSupplierID INT NOT NULL, -- from the Suppliers tableProductID INT NOT NULL, -- from the Products table, if you choose--a ProductID, SupplierID is selected also by inheritenceCategoryID INT NOT NULL, -- from the Categories tableSOLDDFROM DATETIME NULL, -- Sold from which dateSOLDTO DATETIME NULL, -- Sold to which dateMINSALES INT NOT NULL, -- The minimum amount of salesMAXSALES INT NOT NULL, -- The maximum amount of salesOPERATOR TINYINT NOT NULL -- 1 is logical operator AND, 2 is OR)GOINSERT INTO REPORT_SELECTIONSELECT 1, 1, 2, 1, '1/1/1996', '1/1/2000', 10, 10000, 1 UNION ALLSELECT 1, -1, -1, 1, '1/1/1996', '1/1/2000', 10, 1000, 1You can ask all kinds of sales questions like:1-I want all employees that sold products from supplierID 1(Exotic Liquids), specifically the ProductID 2 (Chang) from theCategoryID 1 (Beverages) between Jan 1 1996 to Jan 1 2000 and soldbetween $10 and $10000 - AND for my 2nd sales question2-I want all employees that sold CategoryID 1 (beverages) betweenJan 1 1996 to Jan 1 2000 and sold between $10 and $1000I want to get the common result of both questions and find outwhich employee(s) are in this list.Here are some of the points:1-I want my query to return the list of employees fitting theresult of my sales question(s).2-If I ask three questions with the logical operator AND, I wantthe list of employees that are common to all three questions.3-If I ask 2-3-4. questions with the logical operator OR, I wantthe list of employees that are in the list of the 1st "successful"sales question (the first question that returns any employee isgood enough)4-You can ask all kind of sales question you want even if theycontradict each other. The SP should still run and returnnothing if that is the case.5-Let's assume you can have the same product name from the samesupplier but under different categories. So entering a ProductIDshould not automatically enter the CategoryID also; whereasentering the ProductID should automatically enter its SupplierID.6-SOLDFROM, SOLDTO, MINSALES, MAXSALES, OPERATOR are mandatoryfields, you can't leave them NULL7-SupplierID, ProductID and CategoryID are the dynamic inputparameters, there can be 5 different combinations to choose from:a-SupplierID onlyb-SupplierID and a ProductID,c-SupplierID and a CategoryIDd-SupplierID, ProductID and a CategoryIDe-CategoryID onlyf-Any time you choose a ProductID, the SupplierID valuewill be filled automatically based on the ProductID'srelationshipg-Any of the three values here that is not chosen by theuser will take a default value of -1 (meaning return ALLfor this Column, in other words don't filter by this column)The major problem I have is I can't use dynamic SQL for choosingthe three dynamic columns as the 2nd row of records would have adifferent selection of dynamic columns (at least I don't know howif the solution is dynamic SQL). The only solution I can think oflooks pretty bad to me. I would use a cursor, run each row at atime, store a TRUE, FALSE value to stop processing or not andstore the result in another detail table. Then if all ANDquestions have ended with TRUE do a union of all the result andreturn the common list of employees. It sounds pretty awful as anapproach. I am hoping there's a simpler method for achieving this.Does anyone know if any SQL book has a topic on this type ofquery? If so I'll definitely buy the book.I appreciate any help you can provide.Thank you

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved