Is their best to to execute update query. here is my problem
I have a two table on different databases on sql server 2000...
one called source which has 104 fields and another called des table with
same fields...
i want to check if any thing changed in source update the destination....
now i create a sql string mean uinsg dynamic sql to create where clause cos
if write a query my self it will take ages to write that where clause cos i
am comparing each field with each field using OR statment... then i cannt d
o
it with update so i delete the records from des table and insert again from
source table.... as writing update query need too mush string and also will
be problem to put where clause ....
is what i am doing is ok or any other better idea to overcome that problem
thanksHi,
You may use COLUMNS_UPDATED() function in the trigger to find out the
updated columns. But still you have the difficulty of transferring the
changes to dest table. Probably dynamically creating the update command can
help. If you want to get rid of writing any code, replication is an option!
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:8A6199E7-8F13-4BC3-BBAE-9DC56D699EE6@.microsoft.com...
> Is their best to to execute update query. here is my problem
> I have a two table on different databases on sql server 2000...
> one called source which has 104 fields and another called des table with
> same fields...
> i want to check if any thing changed in source update the destination....
> now i create a sql string mean uinsg dynamic sql to create where clause
> cos
> if write a query my self it will take ages to write that where clause cos
> i
> am comparing each field with each field using OR statment... then i cannt
> do
> it with update so i delete the records from des table and insert again
> from
> source table.... as writing update query need too mush string and also
> will
> be problem to put where clause ....
> is what i am doing is ok or any other better idea to overcome that problem
> thanks|||It would probably take less time to manually write out all 104 columns than
the time you have spent messing with the dynamic SQL. Maybe a few minutes
in any decent text editor?
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:8A6199E7-8F13-4BC3-BBAE-9DC56D699EE6@.microsoft.com...
> Is their best to to execute update query. here is my problem
> I have a two table on different databases on sql server 2000...
> one called source which has 104 fields and another called des table with
> same fields...
> i want to check if any thing changed in source update the destination....
> now i create a sql string mean uinsg dynamic sql to create where clause
cos
> if write a query my self it will take ages to write that where clause cos
i
> am comparing each field with each field using OR statment... then i cannt
do
> it with update so i delete the records from des table and insert again
from
> source table.... as writing update query need too mush string and also
will
> be problem to put where clause ....
> is what i am doing is ok or any other better idea to overcome that problem
> thanks
Showing posts with label execute. Show all posts
Showing posts with label execute. Show all posts
Wednesday, March 21, 2012
Saturday, February 25, 2012
NEED HELP!!! Unknown Error in SQL 2005 express toolkit
I am encountering this error when tried to execute a simple select statement against the Enterprise Edition SQL 2000 and SQl2005
An error occurred while executing batch. Error message is: Unknown error "-1".
Please advise what is the cause of this.
Thanks.
I also encountering it even I upgraded my Management studio toMicrosoft SQL Server Management Studio 9.00.1399.00
Please advise/assist in this error. Thanks.
|||which simple select statement?
are you using Microsoft SQL Server Management Studio Express? with SQL Server 2005 Express Edition or SQL Server 2005 Express Edition with Advanced Services or SQL Server 2005 database engine?
tosc
|||I installed SQL 2005 express toolkit in my PC and tried to connect to a Enterprise Edition SQL 2000 engine and SQL 2005 engine server. I tried for select rows in a table, example,
select * from<tablename>
It returns that error. i uninstall the Express edition toolkit and installed a enterprise version of the management studio. (version on my last post), still i got the same error when I executed that SELECT statement.
|||is this SQL Server 2005 Express or EE ... u have mentioned EE ... in any case it seems that u have not applied SP1 and SP2 .... BTW can u post the script which u ran ...
Madhu
|||I installed SQL 2005 Express Toolkit for the client tool,(Management Studio). my plan is to use this to manage our Enterprise Edition SQL servers(2000 and 2005).
I connect my Management Studio(SQL 2005 Express) to a SQL 2000 server enterprise edition.
I encountered this error:
An error occurred while executing batch. Error message is: Unknown error "-1".
even my script is a simple SELECT statement like this one:
SELECT * FROM <dbo.TableName>
I test the same script to a SQL 2005 Ent. Edition server. same error encountered. I tried a simple UPDATE statement, no error occured. it updates row in the SQL2000 server.
Then I uninstall the Express edition Management Studio(SQL 2005 Express toolkit). I install a Enterprise Edition management Studio(See my previous reply for the version) and still I encounter the same error when selecting data from any table of an Enterprise edition server.
Hope my issue is clear. do I have to install something? please advise.
|||Do I need to install SP1 even if I don't have SQL engine in my PC? i only have client tools in my PC. Please advise.|||
Hi,
which framework and OS are you using? are you sure, that you have all privelegs - to your databases?
|||I have .NET 1.1 and 2.0 in my PC. I am using Win XP SP2. I already applied SP1 for SQL 2005 in my PC. still same error is encountered.
I have SQL 2000 and SQL 2005 Client utilities in my PC.
I didn't encounter it in Query Analyzer. Only when I use Management Studio.
I am using SA account.
|||I have the same issue and have yet to find the resolution. One thing I have noticed is that the issue does not occur if you set your results to Text Mode. The issue appears to be related to mscorlib.dll.
|||Could it be resolved by replacing the same dll with one from a working PC? or do i have to consider something like file version etc.?
|||Hi all,
Any comment/suggestions to resolve this issue?
|||I was able to resolve this issue on my system. It turns out that the client firewall tool used at my company "BlackIce" was detecting a buffer overflow exploit when making the calls to mscorlib.cll. I was able to list "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" as an exception to rule and all is fine.
I hope these findings help you as well.
NEED HELP!!! Unknown Error in SQL 2005 express toolkit
I am encountering this error when tried to execute a simple select statement against the Enterprise Edition SQL 2000 and SQl2005
An error occurred while executing batch. Error message is: Unknown error "-1".
Please advise what is the cause of this.
Thanks.
I also encountering it even I upgraded my Management studio toMicrosoft SQL Server Management Studio 9.00.1399.00
Please advise/assist in this error. Thanks.
|||which simple select statement?
are you using Microsoft SQL Server Management Studio Express? with SQL Server 2005 Express Edition or SQL Server 2005 Express Edition with Advanced Services or SQL Server 2005 database engine?
tosc
|||I installed SQL 2005 express toolkit in my PC and tried to connect to a Enterprise Edition SQL 2000 engine and SQL 2005 engine server. I tried for select rows in a table, example,
select * from<tablename>
It returns that error. i uninstall the Express edition toolkit and installed a enterprise version of the management studio. (version on my last post), still i got the same error when I executed that SELECT statement.
|||is this SQL Server 2005 Express or EE ... u have mentioned EE ... in any case it seems that u have not applied SP1 and SP2 .... BTW can u post the script which u ran ...
Madhu
|||I installed SQL 2005 Express Toolkit for the client tool,(Management Studio). my plan is to use this to manage our Enterprise Edition SQL servers(2000 and 2005).
I connect my Management Studio(SQL 2005 Express) to a SQL 2000 server enterprise edition.
I encountered this error:
An error occurred while executing batch. Error message is: Unknown error "-1".
even my script is a simple SELECT statement like this one:
SELECT * FROM <dbo.TableName>
I test the same script to a SQL 2005 Ent. Edition server. same error encountered. I tried a simple UPDATE statement, no error occured. it updates row in the SQL2000 server.
Then I uninstall the Express edition Management Studio(SQL 2005 Express toolkit). I install a Enterprise Edition management Studio(See my previous reply for the version) and still I encounter the same error when selecting data from any table of an Enterprise edition server.
Hope my issue is clear. do I have to install something? please advise.
|||Do I need to install SP1 even if I don't have SQL engine in my PC? i only have client tools in my PC. Please advise.|||
Hi,
which framework and OS are you using? are you sure, that you have all privelegs - to your databases?
|||
I have .NET 1.1 and 2.0 in my PC. I am using Win XP SP2. I already applied SP1 for SQL 2005 in my PC. still same error is encountered.
I have SQL 2000 and SQL 2005 Client utilities in my PC.
I didn't encounter it in Query Analyzer. Only when I use Management Studio.
I am using SA account.
|||I have the same issue and have yet to find the resolution. One thing I have noticed is that the issue does not occur if you set your results to Text Mode. The issue appears to be related to mscorlib.dll.
|||Could it be resolved by replacing the same dll with one from a working PC? or do i have to consider something like file version etc.?
|||Hi all,
Any comment/suggestions to resolve this issue?
|||I was able to resolve this issue on my system. It turns out that the client firewall tool used at my company "BlackIce" was detecting a buffer overflow exploit when making the calls to mscorlib.cll. I was able to list "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" as an exception to rule and all is fine.
I hope these findings help you as well.
NEED HELP!!! Unknown Error in SQL 2005 express toolkit
I am encountering this error when tried to execute a simple select statement against the Enterprise Edition SQL 2000 and SQl2005
An error occurred while executing batch. Error message is: Unknown error "-1".
Please advise what is the cause of this.
Thanks.
I also encountering it even I upgraded my Management studio toMicrosoft SQL Server Management Studio 9.00.1399.00
Please advise/assist in this error. Thanks.
|||which simple select statement?
are you using Microsoft SQL Server Management Studio Express? with SQL Server 2005 Express Edition or SQL Server 2005 Express Edition with Advanced Services or SQL Server 2005 database engine?
tosc
|||I installed SQL 2005 express toolkit in my PC and tried to connect to a Enterprise Edition SQL 2000 engine and SQL 2005 engine server. I tried for select rows in a table, example,
select * from<tablename>
It returns that error. i uninstall the Express edition toolkit and installed a enterprise version of the management studio. (version on my last post), still i got the same error when I executed that SELECT statement.
|||is this SQL Server 2005 Express or EE ... u have mentioned EE ... in any case it seems that u have not applied SP1 and SP2 .... BTW can u post the script which u ran ...
Madhu
|||I installed SQL 2005 Express Toolkit for the client tool,(Management Studio). my plan is to use this to manage our Enterprise Edition SQL servers(2000 and 2005).
I connect my Management Studio(SQL 2005 Express) to a SQL 2000 server enterprise edition.
I encountered this error:
An error occurred while executing batch. Error message is: Unknown error "-1".
even my script is a simple SELECT statement like this one:
SELECT * FROM <dbo.TableName>
I test the same script to a SQL 2005 Ent. Edition server. same error encountered. I tried a simple UPDATE statement, no error occured. it updates row in the SQL2000 server.
Then I uninstall the Express edition Management Studio(SQL 2005 Express toolkit). I install a Enterprise Edition management Studio(See my previous reply for the version) and still I encounter the same error when selecting data from any table of an Enterprise edition server.
Hope my issue is clear. do I have to install something? please advise.
|||Do I need to install SP1 even if I don't have SQL engine in my PC? i only have client tools in my PC. Please advise.|||
Hi,
which framework and OS are you using? are you sure, that you have all privelegs - to your databases?
|||I have .NET 1.1 and 2.0 in my PC. I am using Win XP SP2. I already applied SP1 for SQL 2005 in my PC. still same error is encountered.
I have SQL 2000 and SQL 2005 Client utilities in my PC.
I didn't encounter it in Query Analyzer. Only when I use Management Studio.
I am using SA account.
|||I have the same issue and have yet to find the resolution. One thing I have noticed is that the issue does not occur if you set your results to Text Mode. The issue appears to be related to mscorlib.dll.
|||Could it be resolved by replacing the same dll with one from a working PC? or do i have to consider something like file version etc.?
|||Hi all,
Any comment/suggestions to resolve this issue?
|||I was able to resolve this issue on my system. It turns out that the client firewall tool used at my company "BlackIce" was detecting a buffer overflow exploit when making the calls to mscorlib.cll. I was able to list "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" as an exception to rule and all is fine.
I hope these findings help you as well.
Need Help! Connection Count to Linked DB2 Server Reaches limit (SQL1040N) when execute dyn
Need Help! Connection Count to Linked DB2 Server Reaches limit
(SQL1040N) when execute dynamic generated OPENQUERY T-SQL
Hello, every body.
I created a linked server to DB2 8.1 database which called
GRR_DB2Server. In my stored procedure p_FetchRawData, I need to read
some data from this linked server GRR_DB2Server and insert them into
local SQLServer table SQLServer_A.
Query to GRR_DB2Server joins 3 large DB2 tables DB2_A, DB2_B, DB2_C
(every table has about 1 million records), and part of the query
condition stored as record in table SQLServer_B in local SQLServer.
At first I directly join these 4 tables in one T-SQL statements, but to
my disappointment I found the performance very low afer some practice.
So I changed the T-SQL to use cursor to loop for fetching every row
data in SQLServer_D condition table to some procedure variables, and
then in this loop I generated dynamic T-SQL string which orgnize the
condition and form one OPENQUERY statement.
The pseud code something like this:
CREATE PROCEDURE p_FetchRawData variable_list
AS
BEGIN
.=2E..
DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM local_condition_table
OPEN condition_cursor
FETCH NEXT FROM condition_cursor INTO
local_variables
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
')'
EXEC @.Dynamic_SQL
END
.=2E..
END
But when execute this stored procedure p_FetchRawData, when the loop
count is too big, then I got the error:
[OLE/DB provider returned message: SQL1040N
=E4=B8=8E=E6=95=B0=E6=8D=AE=E5=BA=93=E7=9B=B8=E8=BF=9E=E7=9A=84=E5=BA=94=E7= =94=A8=E7=A8=8B=E5=BA=8F=E5=B7=B2=E8=BE=BE=E5=88=B0=E6=9C=80=E5=A4=A7=E6=95= =B0=E7=9B=AE=E3=80=82
SQLSTATE=3D57030]
OLE DB error trace [OLE/DB Provider 'IBMDADB2'
IDBInitialize::Initialize returned 0x80040e69].
I understood this error meaning which said too many OPENQUERY
connection. I just wonder why every DYNAMIC T-SQL EXECECUTION keeps
their connections to linked server? How to fail these connections when
every OPENQUERY execution finished?
Thanks.
Regards,
Ling, Xiao-liSorry, the pseud code should lik this, just pseud code, in case someone
will question the pseud code validity:
CREATE PROCEDURE p_FetchRawData variable_list
AS
BEGIN
.=2E..
DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM local_condition_table
OPEN condition_cursor
FETCH NEXT FROM condition_cursor INTO
local_variables
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
')'
EXEC (@.Dynamic_SQL)
FETCH NEXT FROM condition_cursor INTO
local_variables
END
.=2E..
END
alingsjtu@.gmail.com =E5=86=99=E9=81=93=EF=BC=9A
> CREATE PROCEDURE p_FetchRawData variable_list
> AS
> BEGIN
> ...
> DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
> SELECT * FROM local_condition_table
> OPEN condition_cursor
> FETCH NEXT FROM condition_cursor INTO
> local_variables
> WHILE @.@.FETCH_STATUS =3D 0
> BEGIN
> SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
> SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
> ')'
> EXEC @.Dynamic_SQL
> END
> ...
> END
> But when execute this stored procedure p_FetchRawData, when the loop
> count is too big, then I got the error:
> [OLE/DB provider returned message: SQL1040N
> =E4=B8=8E=E6=95=B0=E6=8D=AE=E5=BA=93=E7=9B=B8=E8=BF=9E=E7=9A=84=E5=BA=94==E7=94=A8=E7=A8=8B=E5=BA=8F=E5=B7=B2=E8=BE=BE=E5=88=B0=E6=9C=80=E5=A4=A7=E6==95=B0=E7=9B=AE=E3=80=82
> SQLSTATE=3D57030]
> OLE DB error trace [OLE/DB Provider 'IBMDADB2'
> IDBInitialize::Initialize returned 0x80040e69].
> I understood this error meaning which said too many OPENQUERY
> connection. I just wonder why every DYNAMIC T-SQL EXECECUTION keeps
> their connections to linked server? How to fail these connections when
> every OPENQUERY execution finished?
> > Thanks.
> > Regards,
> Ling, Xiao-li
(SQL1040N) when execute dynamic generated OPENQUERY T-SQL
Hello, every body.
I created a linked server to DB2 8.1 database which called
GRR_DB2Server. In my stored procedure p_FetchRawData, I need to read
some data from this linked server GRR_DB2Server and insert them into
local SQLServer table SQLServer_A.
Query to GRR_DB2Server joins 3 large DB2 tables DB2_A, DB2_B, DB2_C
(every table has about 1 million records), and part of the query
condition stored as record in table SQLServer_B in local SQLServer.
At first I directly join these 4 tables in one T-SQL statements, but to
my disappointment I found the performance very low afer some practice.
So I changed the T-SQL to use cursor to loop for fetching every row
data in SQLServer_D condition table to some procedure variables, and
then in this loop I generated dynamic T-SQL string which orgnize the
condition and form one OPENQUERY statement.
The pseud code something like this:
CREATE PROCEDURE p_FetchRawData variable_list
AS
BEGIN
.=2E..
DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM local_condition_table
OPEN condition_cursor
FETCH NEXT FROM condition_cursor INTO
local_variables
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
')'
EXEC @.Dynamic_SQL
END
.=2E..
END
But when execute this stored procedure p_FetchRawData, when the loop
count is too big, then I got the error:
[OLE/DB provider returned message: SQL1040N
=E4=B8=8E=E6=95=B0=E6=8D=AE=E5=BA=93=E7=9B=B8=E8=BF=9E=E7=9A=84=E5=BA=94=E7= =94=A8=E7=A8=8B=E5=BA=8F=E5=B7=B2=E8=BE=BE=E5=88=B0=E6=9C=80=E5=A4=A7=E6=95= =B0=E7=9B=AE=E3=80=82
SQLSTATE=3D57030]
OLE DB error trace [OLE/DB Provider 'IBMDADB2'
IDBInitialize::Initialize returned 0x80040e69].
I understood this error meaning which said too many OPENQUERY
connection. I just wonder why every DYNAMIC T-SQL EXECECUTION keeps
their connections to linked server? How to fail these connections when
every OPENQUERY execution finished?
Thanks.
Regards,
Ling, Xiao-liSorry, the pseud code should lik this, just pseud code, in case someone
will question the pseud code validity:
CREATE PROCEDURE p_FetchRawData variable_list
AS
BEGIN
.=2E..
DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM local_condition_table
OPEN condition_cursor
FETCH NEXT FROM condition_cursor INTO
local_variables
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
')'
EXEC (@.Dynamic_SQL)
FETCH NEXT FROM condition_cursor INTO
local_variables
END
.=2E..
END
alingsjtu@.gmail.com =E5=86=99=E9=81=93=EF=BC=9A
> CREATE PROCEDURE p_FetchRawData variable_list
> AS
> BEGIN
> ...
> DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
> SELECT * FROM local_condition_table
> OPEN condition_cursor
> FETCH NEXT FROM condition_cursor INTO
> local_variables
> WHILE @.@.FETCH_STATUS =3D 0
> BEGIN
> SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
> SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
> ')'
> EXEC @.Dynamic_SQL
> END
> ...
> END
> But when execute this stored procedure p_FetchRawData, when the loop
> count is too big, then I got the error:
> [OLE/DB provider returned message: SQL1040N
> =E4=B8=8E=E6=95=B0=E6=8D=AE=E5=BA=93=E7=9B=B8=E8=BF=9E=E7=9A=84=E5=BA=94==E7=94=A8=E7=A8=8B=E5=BA=8F=E5=B7=B2=E8=BE=BE=E5=88=B0=E6=9C=80=E5=A4=A7=E6==95=B0=E7=9B=AE=E3=80=82
> SQLSTATE=3D57030]
> OLE DB error trace [OLE/DB Provider 'IBMDADB2'
> IDBInitialize::Initialize returned 0x80040e69].
> I understood this error meaning which said too many OPENQUERY
> connection. I just wonder why every DYNAMIC T-SQL EXECECUTION keeps
> their connections to linked server? How to fail these connections when
> every OPENQUERY execution finished?
> > Thanks.
> > Regards,
> Ling, Xiao-li
Monday, February 20, 2012
Need help writing stored procedure involving dates
I am trying to write a stored procedure which would execute following logic:
- The stored procedure takes 2 optional parameters @.StartDate and @.EndDate
@.StartDate Datetime = null
@.EndDate Datetime = null
- Since the parameters are optional user can enter either one or can leave both blank.
- If user doesnot enter any values for SD (start date) and ED (end date), stored procedure should run select query replacing those values with wildcard character '%' or NULL
- If user enters SD, query should use @.StartDate as the SD and GetDate() as the ED
- If user enters ED, query should use @.EndDate as the ED and MIN() of the Date field as SD
I was able to write query which did almost everything as is stated above expect for incorporating NULLs
The query is as below
CREATE PROCEDURE SearchDocumentTable
@.FName varchar(100) = null,
@.LName varchar(25) = null,
@.ID varchar(9) = null,
@.StartDate Datetime = null,
@.EndDate Datetime = null
AS
IF ( @.StartDate IS NULL)
Select @.StartDate = MIN(DateInputted) from Document
Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'
From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@.FName,'%')
AND LName LIKE ISNULL(@.LName,'%' + NULL)
AND ID LIKE ISNULL(@.ID,'%' + NULL)
AND (DateInputted BETWEEN @.StartDate AND ISNULL(@.EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode
GO
Any help would be appreciated
Thanks in advance :)I'm a little confused. What is your question ?
did you mean except for incoporating NULLS ?
Cheers,
-Kilka|||I am sorry wasnt thinking right when I posted that to the forum in the morning. Let me try to explain my problem with an example.
To make it simple lets say I have a simple table with 3 columns: FName, LName and Date
FName LName Date
Aab 02/05/2005
Abc Bb 02/06/2005
Aaaa Bbb
02/07/2005
Baaaa Bbb
Baaca 02/07/2005
Caa Bbbb 02/07/2005
As can be seen that FName, LName and Date columns accept NULL values.
Now I want to write a stored procedure which takes 4 parameters ( all of them are optional) @.FName, @.LName, @.StartDate and @.EndDate and do a search on this table.
I am having trouble handling these NULL fields.
When I execute the stored procedure, I should get results as follows:
- If user enters @.FName LIKE 'A%', sp should return only the rows where first name matches that format ( no null first name or null last names or null dates should be returned). Result should be first 3 rows with first names: Aab, Abc, Aaaa
- Similarly when searching for start date and end date, procedure should return only the rows which match the date criteria ignoring null first name and last name fields
I hope this example would prove helpful, since everytime I try to write a query I always end up getting rows with null fields.
Thanks again for your time :)|||Do you really have '%' + NULL in the code? I would think you just want '%'.
Are you getting any data back from this query when you have data that looks like your example?|||You guys are good sorry forgot to update the stored procedure.
No, '%' + NULL doesnot return any records. So I changed the stored procedure and use only '%' for all null parameters and adds OR FIELDNAME IS NULL at the end:
CREATE PROCEDURE SearchDocumentTable
@.FName varchar(100) = null,
@.LName varchar(25) = null,
@.ID varchar(9) = null,
@.StartDate Datetime = null,
@.EndDate Datetime = null
AS
IF ( @.StartDate IS NULL)
Select @.StartDate = MIN(DateInputted) from Document
Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'
From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@.FName,'%')
AND (LName LIKE ISNULL(@.LName,'%') OR LName IS NULL)
AND (ID LIKE ISNULL(@.ID,'%') OR ID IS NULL)
AND (DateInputted BETWEEN @.StartDate AND ISNULL(@.EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode
GO
This is the stored procedure and as can be seen it will return null fields when I pass last name, ID or date as the parameter.
Any ideas how can I modify the stored procedure to avoid returning null fields.
Thank you again guys, I really appreciate your help|||Do you get the correct results if you take out OR LName IS NULL and
OR ID IS NULL and OR DateInputted IS NULL?
If this is not the case then please type out using the example data you used above the exact results you would like to see if all the input fields are NULL.|||First, decide on precedence. What if the user passes all three parameters?
Assuming the precedence is LastName, FirstName, Date:Declare @.t Table(Table_Pk int identity(1,1), FirstName varchar(3) Null, LastName varchar(3) Null, EndDate datetime Null)
Insert @.t (FirstName, LastName, EndDate)
Select 'Kim', 'Cat', GetDate()
Union
Select 'Pat', 'Dog', Null
Union
Select 'Ted', Null, GetDate()
Union
Select 'Jim', Null, Null
Union
Select Null, 'Fox', GetDate()
Union
Select Null, 'Fox', Null
Union
Select Null, Null, GetDate()
Union
Select Null, Null, Null
Select * From @.t
Declare @.FirstName varchar(3)
, @.LastName varchar(3)
, @.EndDate datetime
Select @.LastName = 'F'
Select *
From @.t
Where (@.LastName Is Not Null And LastName Like @.LastName + '%')
Or
(@.LastName Is Null And @.FirstName Is Not Null And FirstName Like @.FirstName + '%')
Or
(@.LastName Is Null And @.FirstName Is Null And EndDate > = Coalesce(@.EndDate, '01/01/1950') )|||FYI, if you test for the IF statements and, inside the stored procedure, call a different function for each test to return the final result set, you will have no recompiles. This will make the overall execution of the stored proc much faster when you get larger recordsets. You also might want to consider having the developers use a checkbox for exact match. That sounds stupid, but the users will learn to love it if this table gets extremely large.
- The stored procedure takes 2 optional parameters @.StartDate and @.EndDate
@.StartDate Datetime = null
@.EndDate Datetime = null
- Since the parameters are optional user can enter either one or can leave both blank.
- If user doesnot enter any values for SD (start date) and ED (end date), stored procedure should run select query replacing those values with wildcard character '%' or NULL
- If user enters SD, query should use @.StartDate as the SD and GetDate() as the ED
- If user enters ED, query should use @.EndDate as the ED and MIN() of the Date field as SD
I was able to write query which did almost everything as is stated above expect for incorporating NULLs
The query is as below
CREATE PROCEDURE SearchDocumentTable
@.FName varchar(100) = null,
@.LName varchar(25) = null,
@.ID varchar(9) = null,
@.StartDate Datetime = null,
@.EndDate Datetime = null
AS
IF ( @.StartDate IS NULL)
Select @.StartDate = MIN(DateInputted) from Document
Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'
From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@.FName,'%')
AND LName LIKE ISNULL(@.LName,'%' + NULL)
AND ID LIKE ISNULL(@.ID,'%' + NULL)
AND (DateInputted BETWEEN @.StartDate AND ISNULL(@.EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode
GO
Any help would be appreciated
Thanks in advance :)I'm a little confused. What is your question ?
did you mean except for incoporating NULLS ?
Cheers,
-Kilka|||I am sorry wasnt thinking right when I posted that to the forum in the morning. Let me try to explain my problem with an example.
To make it simple lets say I have a simple table with 3 columns: FName, LName and Date
FName LName Date
Aab 02/05/2005
Abc Bb 02/06/2005
Aaaa Bbb
02/07/2005
Baaaa Bbb
Baaca 02/07/2005
Caa Bbbb 02/07/2005
As can be seen that FName, LName and Date columns accept NULL values.
Now I want to write a stored procedure which takes 4 parameters ( all of them are optional) @.FName, @.LName, @.StartDate and @.EndDate and do a search on this table.
I am having trouble handling these NULL fields.
When I execute the stored procedure, I should get results as follows:
- If user enters @.FName LIKE 'A%', sp should return only the rows where first name matches that format ( no null first name or null last names or null dates should be returned). Result should be first 3 rows with first names: Aab, Abc, Aaaa
- Similarly when searching for start date and end date, procedure should return only the rows which match the date criteria ignoring null first name and last name fields
I hope this example would prove helpful, since everytime I try to write a query I always end up getting rows with null fields.
Thanks again for your time :)|||Do you really have '%' + NULL in the code? I would think you just want '%'.
Are you getting any data back from this query when you have data that looks like your example?|||You guys are good sorry forgot to update the stored procedure.
No, '%' + NULL doesnot return any records. So I changed the stored procedure and use only '%' for all null parameters and adds OR FIELDNAME IS NULL at the end:
CREATE PROCEDURE SearchDocumentTable
@.FName varchar(100) = null,
@.LName varchar(25) = null,
@.ID varchar(9) = null,
@.StartDate Datetime = null,
@.EndDate Datetime = null
AS
IF ( @.StartDate IS NULL)
Select @.StartDate = MIN(DateInputted) from Document
Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'
From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@.FName,'%')
AND (LName LIKE ISNULL(@.LName,'%') OR LName IS NULL)
AND (ID LIKE ISNULL(@.ID,'%') OR ID IS NULL)
AND (DateInputted BETWEEN @.StartDate AND ISNULL(@.EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode
GO
This is the stored procedure and as can be seen it will return null fields when I pass last name, ID or date as the parameter.
Any ideas how can I modify the stored procedure to avoid returning null fields.
Thank you again guys, I really appreciate your help|||Do you get the correct results if you take out OR LName IS NULL and
OR ID IS NULL and OR DateInputted IS NULL?
If this is not the case then please type out using the example data you used above the exact results you would like to see if all the input fields are NULL.|||First, decide on precedence. What if the user passes all three parameters?
Assuming the precedence is LastName, FirstName, Date:Declare @.t Table(Table_Pk int identity(1,1), FirstName varchar(3) Null, LastName varchar(3) Null, EndDate datetime Null)
Insert @.t (FirstName, LastName, EndDate)
Select 'Kim', 'Cat', GetDate()
Union
Select 'Pat', 'Dog', Null
Union
Select 'Ted', Null, GetDate()
Union
Select 'Jim', Null, Null
Union
Select Null, 'Fox', GetDate()
Union
Select Null, 'Fox', Null
Union
Select Null, Null, GetDate()
Union
Select Null, Null, Null
Select * From @.t
Declare @.FirstName varchar(3)
, @.LastName varchar(3)
, @.EndDate datetime
Select @.LastName = 'F'
Select *
From @.t
Where (@.LastName Is Not Null And LastName Like @.LastName + '%')
Or
(@.LastName Is Null And @.FirstName Is Not Null And FirstName Like @.FirstName + '%')
Or
(@.LastName Is Null And @.FirstName Is Null And EndDate > = Coalesce(@.EndDate, '01/01/1950') )|||FYI, if you test for the IF statements and, inside the stored procedure, call a different function for each test to return the final result set, you will have no recompiles. This will make the overall execution of the stored proc much faster when you get larger recordsets. You also might want to consider having the developers use a checkbox for exact match. That sounds stupid, but the users will learn to love it if this table gets extremely large.
Need help writing a custom View in Sql Server
Does anyone knowif the following sql view is possible to write and execute as a view script?
**********find employee matching the given UserID*************
SELECT * FROM Employees WHERE EmployeeID=@.UserID
***********find client matching the given ClientID**********
SELECT *FROM Clients WHERE ClientID=@.ClientID
**********findall contacts and events associated with ClientID*********
SELECT *FROM Contacts WHERE Contact.ClientID=@.ClientID
SELECT *FROM Events WHERE Event.ClientID=@.ClientID
*********selectall audits with Key values matching the primary keys of each client, contact orevent*********
SELECT *FROM Audit Where Key In (Client.ClientID, Contact.ContactID, Event.EventID)
I basicallyneed to find a employee based on its ID. Then I need to find any records from the table Auditwith Key values matching the given fields in the results of any clients, contacts events that were returned from the previous select statements. Is this possible?
You could do something like this: If this is not what you are looking for you need to detail the structure of each of the tables and the columns that would be used in the [Key] column in the Audit table.
Declare @.table table (KeyIdint)INSERT INTO @.table SELECT <column>FROM EmployeesWHERE EmployeeId = @.UserIdINSERT INTO @.table SELECT <column>FROM ClientsWHERE ClientID=@.ClientIDINSERT INTO @.table SELECT <column>FROM ContactsWHERE Contact.ClientID=@.ClientIDINSERT INTO @.table SELECT <column>FROM EventsWHERE ClientID=@.ClientIDSELECT *FROM AuditWHERE [Key]In (SELECT KeyIdFROM @.table)
|||
You can format and execute a string similar to:
CREATE VIEW MyView as Select * from MyTable where ID = 55
But the view will be hardcoded to the ID of 55...probably not very useful.
A view itself cannot take parameters but I read that you can do it with user-defined functions.
|||If you need to pass a parameter to it you need to use a stored procedure, not a view.
Subscribe to:
Posts (Atom)