Bulk Load from Stored Procedure

All of the examples of Bulk Load that I have found use VBScript to Envoke
the bulk load. Is it possible to write and SQL Stored procedure to
accomplish the task? An example of such a procedure would be greatly
appreciated.
Thanks
you could use the OA series of extended sps to do that .
"Scott McKillop" <scott_mckillop_NOSPAM_MAN@.adlt.com_REMOVE_CAPS> wrote in
message news:OCn2ObGNEHA.624@.TK2MSFTNGP11.phx.gbl...
> All of the examples of Bulk Load that I have found use VBScript to Envoke
> the bulk load. Is it possible to write and SQL Stored procedure to
> accomplish the task? An example of such a procedure would be greatly
> appreciated.
> Thanks
>

Bulk insert of bit values

Hi Gang:
I've inherited a database that contains a number of tables with bit value
columns. I am trying to write a script with bulk insert statements to
reconstruct the database. When I export the table data, the bit fields get
converted to string equivalents (True and False.) When the bulk insert
statement for the same table executes, I get errors saying that these values
can't be converted to bits.
Can you tell me how to export the data from my tables into text files so
that the bit values can be re-imported / bulk inserted correctly?Hi Robert,
Should be 1,0 or NULL
In what format did you export the data, if you export them to excel its
getting converted to True or False...
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Robert Burdick [eMVP]" <RobertBurdickeMVP@.discussions.microsoft.com>
schrieb im Newsbeitrag
news:2530932F-BFEC-48D6-B30A-A0C9F03EEC25@.microsoft.com...
> Hi Gang:
> I've inherited a database that contains a number of tables with bit value
> columns. I am trying to write a script with bulk insert statements to
> reconstruct the database. When I export the table data, the bit fields
> get
> converted to string equivalents (True and False.) When the bulk insert
> statement for the same table executes, I get errors saying that these
> values
> can't be converted to bits.
> Can you tell me how to export the data from my tables into text files so
> that the bit values can be re-imported / bulk inserted correctly?
>|||Hi
Valid BIT values are 0, 1 and NULL.
True and False are not supported.
VB.NET, VB, VBScript regard True as -1, so that is not convertible. Most
other languages regard True as +1
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Burdick [eMVP]" <RobertBurdickeMVP@.discussions.microsoft.com> wrote
in message news:2530932F-BFEC-48D6-B30A-A0C9F03EEC25@.microsoft.com...
> Hi Gang:
> I've inherited a database that contains a number of tables with bit value
> columns. I am trying to write a script with bulk insert statements to
> reconstruct the database. When I export the table data, the bit fields
> get
> converted to string equivalents (True and False.) When the bulk insert
> statement for the same table executes, I get errors saying that these
> values
> can't be converted to bits.
> Can you tell me how to export the data from my tables into text files so
> that the bit values can be re-imported / bulk inserted correctly?
>|||I exported it from SQL Server using the import / export wizard. I exported
it as a comma delimited text file. Are there other export options I need to
check to make this work?
"Jens Sü?meyer" wrote:

> Hi Robert,
> Should be 1,0 or NULL
> In what format did you export the data, if you export them to excel its
> getting converted to True or False...
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Robert Burdick [eMVP]" <RobertBurdickeMVP@.discussions.microsoft.com>
> schrieb im Newsbeitrag
> news:2530932F-BFEC-48D6-B30A-A0C9F03EEC25@.microsoft.com...
>
>|||Hi
I don't have any issue with the following:
CREATE TABLE [MyBits] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Bit1] [bit] NOT NULL CONSTRAINT [DF_MyBits_Bit1] DEFAULT (0),
[Bit2] [bit] NOT NULL CONSTRAINT [DF_MyBits_Bit2] DEFAULT (0),
[Bit3] [bit] NOT NULL CONSTRAINT [DF_MyBits_Bit3] DEFAULT (1),
[Bit4] [bit] NOT NULL CONSTRAINT [DF_MyBits_Bit4] DEFAULT (1),
CONSTRAINT [PK_MyBits] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
)
GO
INSERT INTO MYBits ( Bit1 )
SELECT 1
UNION ALL SELECT 0
UNION ALL SELECT 1
UNION ALL SELECT 0
UNION ALL SELECT 1
UNION ALL SELECT 0
SELECT * FROM MyBits ORDER BY ID
/*
Id Bit1 Bit2 Bit3 Bit4
-- -- -- -- --
1 1 0 1 1
2 0 0 1 1
3 1 0 1 1
4 0 0 1 1
5 1 0 1 1
6 0 0 1 1
(6 row(s) affected)
*/
-- Create new table
SELECT * INTO MyBits2 FROM MyBits WHERE 1 = 0
-- Command Prompt
-- bcp test..mybits2 IN mybits.txt -c -T
/* MyBits.txt
1 1 0 1 1
2 0 0 1 1
3 1 0 1 1
4 0 0 1 1
5 1 0 1 1
6 0 0 1 1
*/
-- At command prompt import
-- bcp test..mybits2 IN mybits.txt -c -T
SELECT * FROM MyBits2 ORDER BY ID
/* Output
Id Bit1 Bit2 Bit3 Bit4
-- -- -- -- --
1 1 0 1 1
2 0 0 1 1
3 1 0 1 1
4 0 0 1 1
5 1 0 1 1
6 0 0 1 1
(6 row(s) affected)
*/
Can you post DDL, example data and your statements
http://www.aspfaq.com/etiquett_e.asp?id=5006
John
"Robert Burdick [eMVP]" <RobertBurdickeMVP@.discussions.microsoft.com> wrote
in message news:2530932F-BFEC-48D6-B30A-A0C9F03EEC25@.microsoft.com...
> Hi Gang:
> I've inherited a database that contains a number of tables with bit value
> columns. I am trying to write a script with bulk insert statements to
> reconstruct the database. When I export the table data, the bit fields
> get
> converted to string equivalents (True and False.) When the bulk insert
> statement for the same table executes, I get errors saying that these
> values
> can't be converted to bits.
> Can you tell me how to export the data from my tables into text files so
> that the bit values can be re-imported / bulk inserted correctly?
>|||If it wont work for you, you can cast the values to int, thatll work.
Jens Suessmeyer.
"Robert Burdick [eMVP]" <RobertBurdickeMVP@.discussions.microsoft.com>
schrieb im Newsbeitrag
news:A1EF625E-F6AA-4C3F-AD0F-BFCF8CA98375@.microsoft.com...
> I exported it from SQL Server using the import / export wizard. I
> exported
> it as a comma delimited text file. Are there other export options I need
> to
> check to make this work?
> "Jens Smeyer" wrote:
>|||Hi
It seems that this may be feature of the text file driver! As Jens says you
can cast as an int and it will work. To do this choose the option to specify
a query as the source of your export and then put in a statement like:
SELECT [Id],
CAST([Bit1] AS INT) AS [Bit1],
CAST([Bit2] AS INT) AS [Bit2],
CAST([Bit3] AS INT) AS [Bit3],
CAST([Bit4] AS INT) AS [Bit4]
FROM [MyBits]
Alternatively use BCP as in my other post.
John
"Robert Burdick [eMVP]" <RobertBurdickeMVP@.discussions.microsoft.com> wrote
in message news:A1EF625E-F6AA-4C3F-AD0F-BFCF8CA98375@.microsoft.com...
> I exported it from SQL Server using the import / export wizard. I
> exported
> it as a comma delimited text file. Are there other export options I need
> to
> check to make this work?
> "Jens Smeyer" wrote:
>|||Robert,
Ignoring why you have the strings True and False in your text
file, you can import your data into a staging table that matches
your ultimate destination except for the bit column, which in the
staging table should be varchar(5). Then once the 'True' and 'False'
strings are in the staging table,
insert into Destination
select
nonBitcolA,
nonBitcolB,
cast(case when preBit1 = 'True' then 1 when 'False' then 0 end as bit)
as BitCol1,
..
from Staging
Anything from your text file that is not 'True' or 'False' in the bit
columns will import as NULL. You can check for that either in
the staging table or the destination table with
select * from Staging
where preBit1 not in ('True','False')
or preBit2 not in ('True','False')
...
or
select * from Destination
where BitCol1 is null
or BitCol1 is null
...
Steve Kass
Drew University
Robert Burdick [eMVP] wrote:

>Hi Gang:
>I've inherited a database that contains a number of tables with bit value
>columns. I am trying to write a script with bulk insert statements to
>reconstruct the database. When I export the table data, the bit fields get
>converted to string equivalents (True and False.) When the bulk insert
>statement for the same table executes, I get errors saying that these value
s
>can't be converted to bits.
>Can you tell me how to export the data from my tables into text files so
>that the bit values can be re-imported / bulk inserted correctly?
>
>|||Thanks, I've almost got it. SQL Server Books Online doesn't explain how to
tell BCP to comma dellimit the fields. Any ideas?
"Steve Kass" wrote:

> Robert,
> Ignoring why you have the strings True and False in your text
> file, you can import your data into a staging table that matches
> your ultimate destination except for the bit column, which in the
> staging table should be varchar(5). Then once the 'True' and 'False'
> strings are in the staging table,
> insert into Destination
> select
> nonBitcolA,
> nonBitcolB,
> cast(case when preBit1 = 'True' then 1 when 'False' then 0 end as bit)
> as BitCol1,
> ...
> from Staging
> Anything from your text file that is not 'True' or 'False' in the bit
> columns will import as NULL. You can check for that either in
> the staging table or the destination table with
> select * from Staging
> where preBit1 not in ('True','False')
> or preBit2 not in ('True','False')
> ...
> or
> select * from Destination
> where BitCol1 is null
> or BitCol1 is null
> ...
> Steve Kass
> Drew University
>
> Robert Burdick [eMVP] wrote:
>
>|||>> I've inherited a database that contains a number of tables with bit
value columns. <<
The right answer is to re-design the database from an assembly language
model of data to an RDBMS and get rid of the proprietary BIT data.
reconstruct the database. When I export the table data, the bit fields
[sic] get converted to string equivalents (True and False.) <<
Why do you think that (0,1) mapping to (FALSE, TRUE) is a correct model
for casting BITs. Some host languages use (0,-1) and other use (1,0).
And there are no BOOLEANs in SQL-92 for very good reasons that have
to do with 3VL, NULLs and the data model in SQL.
The fifth labor of Hercules was to clean the stables of King Augeas in
a single day. The Augean stables held thousands of animals and were
over a mile long. This story has a happy ending for three reasons: (1)
Hercules solved the problem in a clever way (2) Hercules got one tenth
of the cattle for his work (3) At the end of the story of the Labors of
Hercules, he got to kill the bastard that gave him this job. Maybe you
will be lucky, too.

Bulk Insert not working in ASP

---------------------------

I have the following ASP code:

'load the conversion_dump_daily table
Response.Write("<br>Load Conversion_Dump_Daily data for " & theDate & ".")
Response.Write("<br> File Name: " & inputFile & ".")
Response.flush
cmd.CommandText = "AddConversionDumpDaily"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("InFile", adVarChar, , 1000, "D:\DataSources\ConversionBuilder\test.txt")
On Error Resume Next
cmd.Execute
cmd.Parameters.Delete("InFile")

..... asp error checking follows

---------------------------

The stored procedure is as follows:

CREATE PROCEDURE AddConversionDumpDaily @.InFile varchar(1000)
AS
DECLARE @.ErrorSave int
SET @.ErrorSave = 0
-- Create a transaction so that a rollback can be performed in case of an error.
BEGIN TRAN
-- Bulk insert Conversion data from csv file.
EXEC('BULK INSERT Conversion_Dump_Daily FROM ''' + @.InFile + ''' WITH (FORMATFILE=''D:\FormatFiles\ConversionBuilder.fmt '') ')

-- Check to see if the insert was successful
if @.@.error <> 0
begin
SET @.ErrorSave = @.@.error -- Store the error code in a variable.
rollback tran
goto endOfBatch
end
COMMIT TRAN
endOfBatch:
RETURN @.ErrorSave -- Return the error code to determine success or failure of the process.
GO

---------------------------

The stored procedure works fine from the Query Analyzer, but nothing is inserted from the ASP page. I don't get any errors... it is just that nothing appears!

Any ideas anyone?I am not sure if you left out the code, but where are you creating the command object and adding the connection information. Also, you will not receive an error when you are using "On Error Resume Next" before your execute statement. By removing this, you will see the problem.

Good luck.

BULK insert from a text file name stored into a variable

Hello I need to write a proc to load data from txt files I receive into a table. It works fine when I specify
bulk insert... from 'myfilename.txt'
BUT my filename will always change and I store it into a variable @.filename

When I try to run the bulk insert instruction ... from @.filename it doesn't work..
do you know why?

Thank you in advanceAccoding to the destructions for BULK INSERT (http://msdn.microsoft.com/library/en-us/tsqlref/ts_ba-bz_4fec.asp), it takes a literal string for the file name. To sidestep this requirement, you can resort to dynamic SQL using the EXECUTE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_4vxn.asp) character string syntax. It isn't pretty, but it should get the job done for you.

-PatP

Bulk Insert and Decimal type

Hi there

I am trying to write a program which will bulk load data from a bcp file into a newly made database on the users PC.

I create the data from an existing DB using SQL-DMO BulkCopy.
I then load it into the users DB using "Bulk Insert " transact SQL.

It all works fine on SQL Server 2000. However on SQL Server 7.0 whenever the .bcp file is being loaded into a table with a field of type decimal, it throws an OLEDB stream error. Even when the .bcp file is empty.

I have tried exporting/importing the data as tab delimited and as native, but it seems to make no difference.

This has really got me stumped and I am running out of time. Can anyone help?

Thanks.

justinOK I just discovered from the Microsoft web site that there is a bug in SQL Server 7.0. Using Bulk Insert on a table that includes a default value for decimal or numeric data typed fields, throws an error.

There is no solution. It is incurable. The workaround is to "use bcp instead."

Programatically that would be an issue, so i will have to use DMO.

Bulk insert

I m a newbie in Stored Proc. Here I m working with some stuff for importing the csv files then write those inside to MSSQL Server. However I get confused what steps I shoud take.

Here's the Stored Proc I've written.

Instead of running execute proc_TestIT '11113333', 'V', 'Tony Jones , those records would be kept in a csv file instead with at least 100 records per each csv file. I am wondering if I should use bulk insert and what I should do with the SP I 've written. As only 10 out of all the 20 columns in the interface file would be required for the updates/inserts. I m wondering where I should start.

execute proc_TestIT '11113333', 'V', 'Tony Jones'
CREATE Procedure proc_TestIT

@.Locker_No Varchar(10),
@.Locker_Type Varchar(10),
@.Member_Full_Name Varchar(30)

AS
declare @.Location Varchar(06)
set @.Location=' '
Begin
BEGIN TRANSACTION
SET @.Location = (select Location from tblLockers where Locker_No=@.Locker_No)
IF (@.Locker_Type='P')
begin
Update tblLockerIssues SET Remarks='Damaged' where Locker_No=@.Locker_No
end
IF(@.Locker_Type='V')
begin
insert into tblLockerIssues_VIP values(@.Locker_No,@.Location,'')
end
COMMIT TRANSACTION
RETURN
End
GO

There are several basic building blocks that you can use:

Consider using SSIS (DTS if you are running SQL Server 2000) instead of a stored procedure|||

Hi Dave,

Thanks a lot. For the stuff I m working with, we have to enable users to import the csv files with the use of ASP.

As such imports would trigger inserts/updates in several tables e.g. by check each record's member_type in the inteface files, I m wondering if I should make use of ASP to extract data from those columns I need from the csv files then import into the DB.

Cheers,

Manfred

Bulk Insert

Hi all

I need to write a bulk insert statement to import some data from a file, as i have not done this before I used BOL to help. The statement is below:

BULK INSERT pubs..publishers2 FROM 'c:\newpubs.dat'
WITH
(
, DATAFILETYPE = 'char'
, FIELDTERMINATOR = ','
, ROWTERMINATOR = '\n'
)

I have created the .dat file with the data, the problem is when i execute the statement i get this error.

Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 2, column 1 (pub_id).
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 3, column 1 (pub_id).

Any help will be good!! ... Thanks Rich

If you did like I did, you used the Import/Export wizard to create the file. You chose your Sql Server as the Source and a Text file as the destination and specified that you wanted to copy a table.

What I failed to notice was that the table that was selected was the authors table and not the publishers table. So that when I imported it with the command you gave above, I received the exact same errors.

However, when I actually copied out the publisher table, it copied back in without error.

Make sure that you have the data that you expect to have in your file. You should only have 8 records (not 23) and the pub-id column should be only 4 characters long.

|||

Thanks for your reply.

I need to use bulk insert statement as it is part of a sp so the wizard unfortunatly is not the option.

Regards... Rich

BULK COPY - in memory data

Hi,

I have a set of records in application memory seperated by a record terminator '\n'. I can write the memory stream to a local disk file and call bcp api functions to load the file in to SQL server. But how do I transfer the in memory data directly to the SQL server, without writing to a data file, using ODBC. I am not using any .Net Framework classes in my code. The SQL server and application server(generating the data records) are on two different physical servers connected through network. I am trying to figureout the fastest and efficient way to load the data to SQL server from a remote application server. Thanks for your help.

Srini

Hi Srini,

You need to use the In-Memory BCP APIs for this purpose. You can look up the MSDN documentation for bcp_bind function which also has a small sample code about how to do it. Here is a link:

http://msdn2.microsoft.com/ru-ru/library/ms131401.aspx

Thanks

Waseem

Building multiple "temp" tables in SQL....

Hello,
Using SQL 2000 and I want to write a query that creates a different "temp"
table every time the query is run so that more than one user cannot be
accessing the same temp table at the same time.
I see two possible ways of doing this.
1.) My app can take the user ID as an input field, and I suppose that I
could use this in the query (as a variable) to generate a separate "temp "
table for each user based on their ID.
For example, I'm Joe and my user ID is 1.
In the app I select my user ID as an input field and I click a button. The
button runs the query which needs to take that User ID and create a temp
table with the ID as part of the table name so that it is different for each
user.
2.) If I can just tell the query to create a differentiated "temp" table
every time it is run (say with an increment function or something) that would
work as well.
My goal is simply to make sure that when the query is run that not more than
one person is accessing the same temp table at the same time.
The table is then truncated or dropped by another process.
Any idea if of how this can be done?
Thanks much,
Mark
Key point: temp tables cannot be access by anyone outside of the current
connection (unless they are global temp tables). Thus you can use the exact
same name (such as #tmp) for every execution and have no worries that they
will step on each other's data.
TheSQLGuru
President
Indicium Resources, Inc.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> hi
> may be you should spend some time to understand what temp tables are,
> read BOL and
> http://www.sqlteam.com/article/temporary-tables
> http://www.sql-server-performance.com/jg_derived_tables.asp
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>
|||Hello,
Point well taken, however I seem to have another problem. It appears that
temp talbes are destroyed after the process is finished? I need my temp
table to "stick around" for a few other queries or SP's to use it, only then
do I need to get rid of it.
Can I somehow just create a separate regular table that can be given a
different name for each user that runs the Sp or SQL code?
Thanks much,
Mark
"TheSQLGuru" wrote:

> Key point: temp tables cannot be access by anyone outside of the current
> connection (unless they are global temp tables). Thus you can use the exact
> same name (such as #tmp) for every execution and have no worries that they
> will step on each other's data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>
>
|||If you need persisted data, temp tables aren't the way to go.
Yes, you CAN create permanent tables that are uniquely named using the
login/dbuser name and perhaps some counter obtained from a sequence table.
Or you could use a GUID for the name. But how would you know what table to
access for follow-on queries/sprocs? If you pass table name as variable -
you are stuck with dynamic sql. Also, how would you clean these objects up?
Are you SURE you must have this type of processing in place?
TheSQLGuru
President
Indicium Resources, Inc.
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...[vbcol=seagreen]
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
|||If you create normal/regular table with name like <user><data code>, same
user will be able to use it. Even after disconnect.
Did you try to create named tables in tempdb? This could solve your issue
too. Just remember they will be kept between sessions, so you'll need to
manage them too.
HTH
Alex
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>

Building multiple "temp" tables in SQL....

Hello,
Using SQL 2000 and I want to write a query that creates a different "temp"
table every time the query is run so that more than one user cannot be
accessing the same temp table at the same time.
I see two possible ways of doing this.
1.) My app can take the user ID as an input field, and I suppose that I
could use this in the query (as a variable) to generate a separate "temp "
table for each user based on their ID.
For example, I'm Joe and my user ID is 1.
In the app I select my user ID as an input field and I click a button. The
button runs the query which needs to take that User ID and create a temp
table with the ID as part of the table name so that it is different for each
user.
2.) If I can just tell the query to create a differentiated "temp" table
every time it is run (say with an increment function or something) that woul
d
work as well.
My goal is simply to make sure that when the query is run that not more than
one person is accessing the same temp table at the same time.
The table is then truncated or dropped by another process.
Any idea if of how this can be done?
Thanks much,
Markhi
may be you should spend some time to understand what temp tables are, read
BOL and
http://www.sqlteam.com/article/temporary-tables
http://www.sql-server-performance.c...ived_tables.asp
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
> Hello,
> Using SQL 2000 and I want to write a query that creates a different "temp"
> table every time the query is run so that more than one user cannot be
> accessing the same temp table at the same time.
> I see two possible ways of doing this.
> 1.) My app can take the user ID as an input field, and I suppose that I
> could use this in the query (as a variable) to generate a separate "temp "
> table for each user based on their ID.
> For example, I'm Joe and my user ID is 1.
> In the app I select my user ID as an input field and I click a button.
> The
> button runs the query which needs to take that User ID and create a temp
> table with the ID as part of the table name so that it is different for
> each
> user.
> 2.) If I can just tell the query to create a differentiated "temp" table
> every time it is run (say with an increment function or something) that
> would
> work as well.
> My goal is simply to make sure that when the query is run that not more
> than
> one person is accessing the same temp table at the same time.
> The table is then truncated or dropped by another process.
> Any idea if of how this can be done?
> Thanks much,
> Mark
>|||Key point: temp tables cannot be access by anyone outside of the current
connection (unless they are global temp tables). Thus you can use the exact
same name (such as #tmp) for every execution and have no worries that they
will step on each other's data.
TheSQLGuru
President
Indicium Resources, Inc.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> hi
> may be you should spend some time to understand what temp tables are,
> read BOL and
> http://www.sqlteam.com/article/temporary-tables
> http://www.sql-server-performance.c...ived_tables.asp
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>|||Hello,
Point well taken, however I seem to have another problem. It appears that
temp talbes are destroyed after the process is finished? I need my temp
table to "stick around" for a few other queries or SP's to use it, only then
do I need to get rid of it.
Can I somehow just create a separate regular table that can be given a
different name for each user that runs the Sp or SQL code?
Thanks much,
Mark
"TheSQLGuru" wrote:

> Key point: temp tables cannot be access by anyone outside of the current
> connection (unless they are global temp tables). Thus you can use the exa
ct
> same name (such as #tmp) for every execution and have no worries that they
> will step on each other's data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>
>|||If you need persisted data, temp tables aren't the way to go.
Yes, you CAN create permanent tables that are uniquely named using the
login/dbuser name and perhaps some counter obtained from a sequence table.
Or you could use a GUID for the name. But how would you know what table to
access for follow-on queries/sprocs? If you pass table name as variable -
you are stuck with dynamic sql. Also, how would you clean these objects up?
Are you SURE you must have this type of processing in place'
TheSQLGuru
President
Indicium Resources, Inc.
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...[vbcol=seagreen]
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>|||If you create normal/regular table with name like <user><data code>, same
user will be able to use it. Even after disconnect.
Did you try to create named tables in tempdb? This could solve your issue
too. Just remember they will be kept between sessions, so you'll need to
manage them too.
HTH
Alex
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>
>

Building multiple "temp" tables in SQL....

Hello,
Using SQL 2000 and I want to write a query that creates a different "temp"
table every time the query is run so that more than one user cannot be
accessing the same temp table at the same time.
I see two possible ways of doing this.
1.) My app can take the user ID as an input field, and I suppose that I
could use this in the query (as a variable) to generate a separate "temp "
table for each user based on their ID.
For example, I'm Joe and my user ID is 1.
In the app I select my user ID as an input field and I click a button. The
button runs the query which needs to take that User ID and create a temp
table with the ID as part of the table name so that it is different for each
user.
2.) If I can just tell the query to create a differentiated "temp" table
every time it is run (say with an increment function or something) that would
work as well.
My goal is simply to make sure that when the query is run that not more than
one person is accessing the same temp table at the same time.
The table is then truncated or dropped by another process.
Any idea if of how this can be done?
Thanks much,
Markhi
may be you should spend some time to understand what temp tables are, read
BOL and
http://www.sqlteam.com/article/temporary-tables
http://www.sql-server-performance.com/jg_derived_tables.asp
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
> Hello,
> Using SQL 2000 and I want to write a query that creates a different "temp"
> table every time the query is run so that more than one user cannot be
> accessing the same temp table at the same time.
> I see two possible ways of doing this.
> 1.) My app can take the user ID as an input field, and I suppose that I
> could use this in the query (as a variable) to generate a separate "temp "
> table for each user based on their ID.
> For example, I'm Joe and my user ID is 1.
> In the app I select my user ID as an input field and I click a button.
> The
> button runs the query which needs to take that User ID and create a temp
> table with the ID as part of the table name so that it is different for
> each
> user.
> 2.) If I can just tell the query to create a differentiated "temp" table
> every time it is run (say with an increment function or something) that
> would
> work as well.
> My goal is simply to make sure that when the query is run that not more
> than
> one person is accessing the same temp table at the same time.
> The table is then truncated or dropped by another process.
> Any idea if of how this can be done?
> Thanks much,
> Mark
>|||Key point: temp tables cannot be access by anyone outside of the current
connection (unless they are global temp tables). Thus you can use the exact
same name (such as #tmp) for every execution and have no worries that they
will step on each other's data.
--
TheSQLGuru
President
Indicium Resources, Inc.
"vt" <vinu.t.1976@.gmail.com> wrote in message
news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> hi
> may be you should spend some time to understand what temp tables are,
> read BOL and
> http://www.sqlteam.com/article/temporary-tables
> http://www.sql-server-performance.com/jg_derived_tables.asp
>
> regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>> Hello,
>> Using SQL 2000 and I want to write a query that creates a different
>> "temp"
>> table every time the query is run so that more than one user cannot be
>> accessing the same temp table at the same time.
>> I see two possible ways of doing this.
>> 1.) My app can take the user ID as an input field, and I suppose that I
>> could use this in the query (as a variable) to generate a separate "temp
>> "
>> table for each user based on their ID.
>> For example, I'm Joe and my user ID is 1.
>> In the app I select my user ID as an input field and I click a button.
>> The
>> button runs the query which needs to take that User ID and create a temp
>> table with the ID as part of the table name so that it is different for
>> each
>> user.
>> 2.) If I can just tell the query to create a differentiated "temp" table
>> every time it is run (say with an increment function or something) that
>> would
>> work as well.
>> My goal is simply to make sure that when the query is run that not more
>> than
>> one person is accessing the same temp table at the same time.
>> The table is then truncated or dropped by another process.
>> Any idea if of how this can be done?
>> Thanks much,
>> Mark
>>
>|||Hello,
Point well taken, however I seem to have another problem. It appears that
temp talbes are destroyed after the process is finished? I need my temp
table to "stick around" for a few other queries or SP's to use it, only then
do I need to get rid of it.
Can I somehow just create a separate regular table that can be given a
different name for each user that runs the Sp or SQL code?
Thanks much,
Mark
"TheSQLGuru" wrote:
> Key point: temp tables cannot be access by anyone outside of the current
> connection (unless they are global temp tables). Thus you can use the exact
> same name (such as #tmp) for every execution and have no worries that they
> will step on each other's data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "vt" <vinu.t.1976@.gmail.com> wrote in message
> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> > hi
> >
> > may be you should spend some time to understand what temp tables are,
> > read BOL and
> >
> > http://www.sqlteam.com/article/temporary-tables
> > http://www.sql-server-performance.com/jg_derived_tables.asp
> >
> >
> >
> > regards
> > VT
> > Knowledge is power, share it...
> > http://oneplace4sql.blogspot.com/
> >
> >
> > "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> > news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
> >> Hello,
> >>
> >> Using SQL 2000 and I want to write a query that creates a different
> >> "temp"
> >> table every time the query is run so that more than one user cannot be
> >> accessing the same temp table at the same time.
> >>
> >> I see two possible ways of doing this.
> >>
> >> 1.) My app can take the user ID as an input field, and I suppose that I
> >> could use this in the query (as a variable) to generate a separate "temp
> >> "
> >> table for each user based on their ID.
> >>
> >> For example, I'm Joe and my user ID is 1.
> >>
> >> In the app I select my user ID as an input field and I click a button.
> >> The
> >> button runs the query which needs to take that User ID and create a temp
> >> table with the ID as part of the table name so that it is different for
> >> each
> >> user.
> >>
> >> 2.) If I can just tell the query to create a differentiated "temp" table
> >> every time it is run (say with an increment function or something) that
> >> would
> >> work as well.
> >>
> >> My goal is simply to make sure that when the query is run that not more
> >> than
> >> one person is accessing the same temp table at the same time.
> >>
> >> The table is then truncated or dropped by another process.
> >>
> >> Any idea if of how this can be done?
> >>
> >> Thanks much,
> >>
> >> Mark
> >>
> >>
> >
> >
>
>|||If you need persisted data, temp tables aren't the way to go.
Yes, you CAN create permanent tables that are uniquely named using the
login/dbuser name and perhaps some counter obtained from a sequence table.
Or you could use a GUID for the name. But how would you know what table to
access for follow-on queries/sprocs? If you pass table name as variable -
you are stuck with dynamic sql. Also, how would you clean these objects up?
Are you SURE you must have this type of processing in place'
--
TheSQLGuru
President
Indicium Resources, Inc.
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>> Key point: temp tables cannot be access by anyone outside of the current
>> connection (unless they are global temp tables). Thus you can use the
>> exact
>> same name (such as #tmp) for every execution and have no worries that
>> they
>> will step on each other's data.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "vt" <vinu.t.1976@.gmail.com> wrote in message
>> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>> > hi
>> >
>> > may be you should spend some time to understand what temp tables are,
>> > read BOL and
>> >
>> > http://www.sqlteam.com/article/temporary-tables
>> > http://www.sql-server-performance.com/jg_derived_tables.asp
>> >
>> >
>> >
>> > regards
>> > VT
>> > Knowledge is power, share it...
>> > http://oneplace4sql.blogspot.com/
>> >
>> >
>> > "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
>> > news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>> >> Hello,
>> >>
>> >> Using SQL 2000 and I want to write a query that creates a different
>> >> "temp"
>> >> table every time the query is run so that more than one user cannot be
>> >> accessing the same temp table at the same time.
>> >>
>> >> I see two possible ways of doing this.
>> >>
>> >> 1.) My app can take the user ID as an input field, and I suppose that
>> >> I
>> >> could use this in the query (as a variable) to generate a separate
>> >> "temp
>> >> "
>> >> table for each user based on their ID.
>> >>
>> >> For example, I'm Joe and my user ID is 1.
>> >>
>> >> In the app I select my user ID as an input field and I click a button.
>> >> The
>> >> button runs the query which needs to take that User ID and create a
>> >> temp
>> >> table with the ID as part of the table name so that it is different
>> >> for
>> >> each
>> >> user.
>> >>
>> >> 2.) If I can just tell the query to create a differentiated "temp"
>> >> table
>> >> every time it is run (say with an increment function or something)
>> >> that
>> >> would
>> >> work as well.
>> >>
>> >> My goal is simply to make sure that when the query is run that not
>> >> more
>> >> than
>> >> one person is accessing the same temp table at the same time.
>> >>
>> >> The table is then truncated or dropped by another process.
>> >>
>> >> Any idea if of how this can be done?
>> >>
>> >> Thanks much,
>> >>
>> >> Mark
>> >>
>> >>
>> >
>> >
>>|||If you create normal/regular table with name like <user><data code>, same
user will be able to use it. Even after disconnect.
Did you try to create named tables in tempdb? This could solve your issue
too. Just remember they will be kept between sessions, so you'll need to
manage them too.
HTH
Alex
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:EBC0E75A-E20B-4D2E-A8E3-AC6E509671B3@.microsoft.com...
> Hello,
> Point well taken, however I seem to have another problem. It appears that
> temp talbes are destroyed after the process is finished? I need my temp
> table to "stick around" for a few other queries or SP's to use it, only
> then
> do I need to get rid of it.
> Can I somehow just create a separate regular table that can be given a
> different name for each user that runs the Sp or SQL code?
> Thanks much,
> Mark
>
> "TheSQLGuru" wrote:
>> Key point: temp tables cannot be access by anyone outside of the current
>> connection (unless they are global temp tables). Thus you can use the
>> exact
>> same name (such as #tmp) for every execution and have no worries that
>> they
>> will step on each other's data.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "vt" <vinu.t.1976@.gmail.com> wrote in message
>> news:uJa16GPrHHA.3228@.TK2MSFTNGP03.phx.gbl...
>> > hi
>> >
>> > may be you should spend some time to understand what temp tables are,
>> > read BOL and
>> >
>> > http://www.sqlteam.com/article/temporary-tables
>> > http://www.sql-server-performance.com/jg_derived_tables.asp
>> >
>> >
>> >
>> > regards
>> > VT
>> > Knowledge is power, share it...
>> > http://oneplace4sql.blogspot.com/
>> >
>> >
>> > "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
>> > news:1AAF1CEB-6FA8-4EF9-85EE-A2A91E10D39D@.microsoft.com...
>> >> Hello,
>> >>
>> >> Using SQL 2000 and I want to write a query that creates a different
>> >> "temp"
>> >> table every time the query is run so that more than one user cannot be
>> >> accessing the same temp table at the same time.
>> >>
>> >> I see two possible ways of doing this.
>> >>
>> >> 1.) My app can take the user ID as an input field, and I suppose that
>> >> I
>> >> could use this in the query (as a variable) to generate a separate
>> >> "temp
>> >> "
>> >> table for each user based on their ID.
>> >>
>> >> For example, I'm Joe and my user ID is 1.
>> >>
>> >> In the app I select my user ID as an input field and I click a button.
>> >> The
>> >> button runs the query which needs to take that User ID and create a
>> >> temp
>> >> table with the ID as part of the table name so that it is different
>> >> for
>> >> each
>> >> user.
>> >>
>> >> 2.) If I can just tell the query to create a differentiated "temp"
>> >> table
>> >> every time it is run (say with an increment function or something)
>> >> that
>> >> would
>> >> work as well.
>> >>
>> >> My goal is simply to make sure that when the query is run that not
>> >> more
>> >> than
>> >> one person is accessing the same temp table at the same time.
>> >>
>> >> The table is then truncated or dropped by another process.
>> >>
>> >> Any idea if of how this can be done?
>> >>
>> >> Thanks much,
>> >>
>> >> Mark
>> >>
>> >>
>> >
>> >
>>
>

Building a Where Clause

I am not very experienced with stored procs and I'm attempting to write my first one. I am writing a search page via aspx and that page will call my proc and depending on the input parameters, the proc will return the search results. To do this I have built a where clause string but I don't know how to (if it's even possible) make this variable part of my query. Can anyone tell me a way to make the following work (input params left out to conserve space)?

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNTON;

SET ANSI_WARNINGSOFF

SET @.where=''

IF @.JobNoStart!=''SET @.where=+' AND LJOB BETWEEN @.JobNoStart AND @.JobNoEnd'

IF @.OrderDateStart!=''SET @.where=+' AND JOBDATE BETWEEN @.OrderDateStart AND @.OrderDateEnd'

IF @.DueDateStart!=''SET @.where=+' AND DUEDATE BETWEEN @.DueDateStart AND @.DueDateEnd'

IF @.ProofDateStart!=''SET @.where=+' AND PROOFDUE BETWEEN @.ProofDateStart AND @.ProofDateEnd'

IF @.CloseDateStart!=''SET @.where=+' AND CLOSEDATE BETWEEN @.CloseDateStart AND @.CloseDateEnd'

IF @.CogsDateStart!=''SET @.where=+' AND COGSDATE BETWEEN @.CogsDateStart AND @.CogsDateEnd'

IF @.ProductName!=''SET @.where=+' AND PRODUCT = @.ProductName'

IF @.CustomerNumber!=''SET @.where=+' AND FCUSTNO = @.CustomerNumber'

IF @.SalesPerson!=''SET @.where=+' AND FSALESPN = @.SalesPerson'

IF @.CSR!=''SET @.where=+' AND JOBPER = @.CSR'

IF @.Closed= 0SET @.where=+' AND CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL'

ELSEIF @.Closed= 1SET @.where=+' AND CLOSEDATE IS NULL'

ELSEIF @.Closed= 2SET @.where=+' AND CLOSEDATE IS NOT NULL'

IF @.Canceled= 0SET @.where=+' AND CANCDATE IS NOT NULL OR CANCDATE IS NULL'

ELSEIF @.Canceled= 1SET @.where=+' AND CANCDATE IS NOT NULL'

ELSEIF @.Canceled= 2SET @.where=+' AND CANCDATE IS NULL'

IF @.FinalShip= 0SET @.where=+' AND FINALSHIP IS NOT NULL OR FINALSHIP IS NULL'

ELSEIF @.FinalShip= 1SET @.where=+' AND FINALSHIP IS NOT NULL'

ELSEIF @.FinalShip= 2SET @.where=+' AND FINALSHIP IS NULL'

SELECT LJOB, DUEDATE, FCOMPANY, ID, QUANWHERE LJOBISNOTNULL @.where

END

To answer my own post, this is how you build a dynamic query in a stored proc:

ALTERPROCEDURE [dbo].[cg_JobSearch]

-- Add the parameters for the stored procedure here

@.JobNoStart varchar(10)='',

@.JobNoEnd varchar(10)= @.JobNoStart,

@.OrderDateStart varchar(10)='',

@.OrderDateEnd varchar(10)= @.OrderDateStart,

@.DueDateStart varchar(10)='',

@.DueDateEnd varchar(10)= @.DueDateStart,

@.ProofDateStart varchar(10)='',

@.ProofDateEnd varchar(10)= @.ProofDateStart,

@.CloseDateStart varchar(10)='',

@.CloseDateEnd varchar(10)= @.CloseDateStart,

@.CogsDateStart varchar(10)='',

@.CogsDateEnd varchar(10)= @.CogsDateStart,

@.ProductName varchar(200)='',

@.CustomerNumber varchar(12)='',

@.SalesPerson varchar(3)='',

@.CSR varchar(15)='',

@.Closedint= 0,

@.Canceledint= 0,

@.FinalShipint= 0,

--@.Invoiced int = 0,

@.where varchar(8000)='',

@.sql varchar(8000)=''

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNTON;

SET ANSI_WARNINGSOFF

SET @.where=''

IF @.JobNoStart!=''SET @.where= @.where+' AND LJOB BETWEEN '+ @.JobNoStart+' AND '+ @.JobNoEnd

IF @.OrderDateStart!=''SET @.where= @.where+' AND JOBDATE BETWEEN '+ @.OrderDateStart+' AND '+ @.OrderDateEnd

IF @.DueDateStart!=''SET @.where= @.where+' AND DUEDATE BETWEEN '+ @.DueDateStart+' AND '+ @.DueDateEnd

IF @.ProofDateStart!=''SET @.where= @.where+' AND PROOFDUE BETWEEN '+ @.ProofDateStart+' AND '+ @.ProofDateEnd

IF @.CloseDateStart!=''SET @.where= @.where+' AND CLOSEDATE BETWEEN '+ @.CloseDateStart+' AND '+ @.CloseDateEnd

IF @.CogsDateStart!=''SET @.where= @.where+' AND COGSDATE BETWEEN '+ @.CogsDateStart+' AND '+ @.CogsDateEnd

IF @.ProductName!=''SET @.where= @.where+' AND PRODUCT = '+ @.ProductName

IF @.CustomerNumber!=''SET @.where= @.where+' AND FCUSTNO = '+ @.CustomerNumber

IF @.SalesPerson!=''SET @.where= @.where+' AND FSALESPN = '+ @.SalesPerson

IF @.CSR!=''SET @.where= @.where+' AND JOBPER = '+ @.CSR

IF @.Closed= 0SET @.where= @.where+' AND CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL'

ELSEIF @.Closed= 1SET @.where= @.where+' AND CLOSEDATE IS NULL'

ELSEIF @.Closed= 2SET @.where= @.where+' AND CLOSEDATE IS NOT NULL'

IF @.Canceled= 0SET @.where= @.where+' AND CANCDATE IS NOT NULL OR CANCDATE IS NULL'

ELSEIF @.Canceled= 1SET @.where= @.where+' AND CANCDATE IS NOT NULL'

ELSEIF @.Canceled= 2SET @.where= @.where+' AND CANCDATE IS NULL'

IF @.FinalShip= 0SET @.where= @.where+' AND FINALSHIP IS NOT NULL OR FINALSHIP IS NULL'

ELSEIF @.FinalShip= 1SET @.where= @.where+' AND FINALSHIP IS NOT NULL'

ELSEIF @.FinalShip= 2SET @.where= @.where+' AND FINALSHIP IS NULL'

SET @.sql='SELECT LJOB, DUEDATE, FCOMPANY, ID, QUAN FROM BBJTHEAD WHERE LJOB IS NOT NULL'

SET @.sql= @.sql+ @.where

PRINT @.sql

EXEC(@.sql)

END

|||

SELECT LJOB, DUEDATE, FCOMPANY, ID, QUANWHERE LJOBISNOTNULL
AND ((@.JobNoStart='') OR (LJOB BETWEEN @.JobNoStart AND @.JobNoEnd))
AND ...
AND ((@.Closed<>0) OR ( CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL))
AND ((@.Closed<>1) OR (CLOSEDATE IS NULL))
AND ((@.Closed<>2) OR (CLOSEDATE IS NOT NULL))
AND ...

Which if you see the pattern, I flip the condition of your IF for the first part, and use the where condition in each of your IFs as the second part. Like:
AND ((reversed IF condition) OR (Your where clause minus the AND))

The SQL in pink is also worthless, it will always be true. Also, the way you are concatenating your OR's and AND's will cause your where clause to not behave the way you want. AND has precedence over OR, where it looks like you want OR to have precedence over AND. As written, if Closed, FinalShip, or Cancelled is 0, you will return data that I suspect you didn't want returned.

If you use EXEC like the above poster suggested (after you fix the AND/OR problems I mentioned, and some errors the above poster made), you'll end up with a SP that can suffer SQL Injection. You are much better off either using the approach I mentioned above, or using the stored procedure that takes both a query string, and a parameter string (Which is a bit more difficult to set up, which is why unless performance is really bad, I use the above). I believe you are looking for the stored procedure sp_ExecuteSQL.

|||Thanks for the advice. It is true that with my current proc I'm susceptable to an injection attack. That would not be good.|||

This is how I ended up doing mine.

CREATE PROCEDURE [dbo].[pe_getAppraisals]

-- Add the parameters for the stored procedure here

@.PTypenvarChar(500),

@.ClientnvarChar(500),

@.PageSizeINT

AS

DECLARE

@.l_SelectnvarChar(4000),

@.l_FromnvarChar(4000),

@.l_SetWherebit,

@.l_PTypenvarChar(500),

@.l_ClientnvarChar(500)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

--Initialize SetWhere to test if a parameter has Added the keyword WHERE

--Initialize the Where statement in case all parameters are null

SET @.l_SetWhere= 0

--Create WHERE portion of the SQL SELECT Statement

IF(@.PTypeISNOTNULL)AND(@.PType<>'')

BEGIN

SET @.l_PType=' WHERE o.PropertyTypeID='+ @.PType

SET @.l_SetWhere= 1

End

ELSESET @.PType=''

IF(@.ClientISNOTNULL)AND(@.Client<>'')

BEGIN

IF @.l_SetWhere= 0

BEGIN

SET @.l_Client=' WHERE o.ClientID='+ @.Client

SET @.l_SetWhere= 1

END

ELSESET @.l_Client=' AND o.ClientID='+ @.Client

END

ELSESET @.l_Client=''

--Build the SQL SELECT Statement

SET @.l_Select=

'o.OrderID, o.FileNumber, o.OrderDate, o.ClientID, o.ClientFileNumber, o.PropertyTypeID, o.EstimatedValue, o.PurchaseValue,

o.LoanOfficer, o.ReportFee, o.FeeBillInd, o.FeeCollectInd, o.CollectAmt, o.Borrower, o.StreetAddrA, o.StreetAddrB, o.City, o.State, o.Zip,

o.ContactName, o.PhoneA, o.PhoneB, o.ApptDate, o.ApptTime, o.AppraiserID, o.InspectionDate, o.DateMailed, o.TrackingInfo, o.ReviewedBy,

o.StatusID, o.Comments, o.SpecialNotes, o.EmailInd, o.MgmtName, o.MgmtContactName, o.MgmtAddress, o.MgmtPhone, o.MgmtFax,

o.MgmtFee, o.MgmtNotes, o.LoginName, on1.NotesDesc AS PreNotesDesc, on2.NotesDesc AS PostNotesDesc, os.StatusDesc,

ot.ReportDesc, ot.ReportFee AS ReportPrice, ot.ReportSeq, pc.PriceDesc, pt.PropertyTypeDesc, l.LoginName AS AppraiserName'

SET @.l_From=

'Orders AS o LEFT OUTER JOIN

OrderNotes AS on1 ON o.PreNotesID = on1.NotesID LEFT OUTER JOIN

OrderNotes AS on2 ON o.PostNotesID = on2.NotesID LEFT OUTER JOIN

OrderStatus AS os ON o.StatusID = os.StatusID LEFT OUTER JOIN

OrderTypes AS ot ON o.ReportID = ot.ReportID LEFT OUTER JOIN

PriceCodes AS pc ON ot.PriceID = pc.PriceID LEFT OUTER JOIN

PropertyTypes AS pt ON o.PropertyTypeID = pt.PropertyTypeID LEFT OUTER JOIN

Logins AS l ON o.AppraiserID = l.LoginID'

Execute('SELECT TOP('+ @.PageSize+') '+ @.l_Select+' FROM '+ @.l_From+ @.l_PType+ @.l_Client)

build sql script in excel

Hi,
I have been given an excel spreadsheet with two columns with data.
There are about 20 records in this excel sheet.
Would like to write an insert query to insert these data.
I am thinking of writing an insert query for the first line iin the excel sheet and then drag it down to the last row of data so that it automatically writes the values of the columns in the insert query and i just copy and paste the script into sql to run.
This is what I have but the values of the cells do not get reflected.
Any thoughts pls?
'insert into TBLData (RE_ID, EMAIL) values (' & c2 & ',' & d2 ')'I used a similar thing just today.

I wrote the first cell as:
"INSERT INTO into TBLData (RE_ID, EMAIL) "

then dragged the following formula down:

=" SELECT '" & c2 & "', '" & d2 & "' UNION ALL "

Copy -> Paste to QA and remove the last UNION ALL

HTH