|
|
You're in the
sql server programming
group:Testing Table Existence - DB name as variable ?
sql server programming:
BEGIN Print 'Do nothing' END ELSE BEGIN Print 'Do something' End For your dynamic sql version you would have to use sp_executesql to set a varriable and use that in the if statement. see http://www.nigelrivett.net/sp_executeSQL.html [quoted text, click to view] "rob" wrote:
> I am doing something wrong in the code below... > > This section fails within the cursor... I am attempting to check for > existence of a table in the database names I am looping through... > > As is, Error is Incorrect syntax near keyword 'ELSE' > If I comment out from BEGIN to the END, Error is Incorrect syntax near ')' > > Thanks ! > > DECLARE @sql as varchar(8000) > DECLARE @SalespersonDB as varchar(8000) > > set @SalespersonDB = 'EJ' > set @sql = ' if exists (select * from dbo.sysobjects where id = object_id([' > + @SalespersonDB + '].[dbo].[tblCustPrClChanlSlsREP]))' > print @sql > Exec(@sql) > BEGIN > Print 'Do nothing' > END > ELSE > BEGIN > Print 'Do something' > End > >
I am doing something wrong in the code below...
This section fails within the cursor... I am attempting to check for existence of a table in the database names I am looping through... As is, Error is Incorrect syntax near keyword 'ELSE' If I comment out from BEGIN to the END, Error is Incorrect syntax near ')' Thanks ! DECLARE @sql as varchar(8000) DECLARE @SalespersonDB as varchar(8000) set @SalespersonDB = 'EJ' set @sql = ' if exists (select * from dbo.sysobjects where id = object_id([' + @SalespersonDB + '].[dbo].[tblCustPrClChanlSlsREP]))' print @sql Exec(@sql) BEGIN Print 'Do nothing' END ELSE BEGIN Print 'Do something' End
If you aren't worried about the object type then
if object_id(@SalespersonDB + '..tblCustPrClChanlSlsREP') is not null For your query better to do an exists rather than a count - will stop if it fnds one rather than scanning the whole table. DECLARE @sql as nvarchar(4000) DECLARE @SalespersonDB as varchar(8000) DECLARE @cnt as varchar(10) select @SalespersonDB = 'Ej', @cnt = 'notexists' set @sql = 'Select @cnt = ''exists'' where exists (select * FROM ' + @SalespersonDB + '.dbo.sysobjects WHERE (xtype = ''U'') and name = ''tblCustPrClChanlSlsREP'')' print @sql Exec sp_executesql @sql, N'@cnt varchar(10) OUTPUT', @cnt OUTPUT print @cnt Nigel Rivett www.nigelrivett.net *** Sent via Devdex http://www.devdex.com ***
4000 is the max length for nvarchar. Should be:
DECLARE @sql as nvarchar(4000) -- Hope this helps. Dan Guzman SQL Server MVP [quoted text, click to view] "Imran Koradia" <nojunk@microsoft.com> wrote in message news:eqt4MyGXEHA.3120@TK2MSFTNGP12.phx.gbl... > thats what sql server expects the type of @sql. declare ur @sql variable as: > DECLARE @sql as nvarchar(8000) > > check out http://www.sommarskog.se/dynamic_sql.html for more info. > > hope this helps.. > > "rob" <rwc1960@bellsouth.net> wrote in message > news:f4DDc.1087$JP6.996@bignews3.bellsouth.net... > > Hi Roji, > > > > Changed it to the following in order to store the results to a variable... > > > > DECLARE @sql as varchar(8000) > > DECLARE @SalespersonDB as varchar(8000) > > DECLARE @cnt as nvarchar(10) > > > > set @SalespersonDB = 'EJ' > > > > set @sql = 'Select @cnt = cast(Count(*) as nvarchar(10)) FROM ' + > > @SalespersonDB + '.dbo.sysobjects WHERE (xtype = ''U'') and name = > > ''tblCustPrClChanlSlsREP''' > > print @sql > > Exec sp_executesql @sql, N'@cnt nvarchar(10) OUTPUT', @cnt OUTPUT > > print @cnt > > > > Resulted in ERROR.... > > Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 10 > > Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. > > > > > > > > "Roji. P. Thomas" <lazydragon@nowhere.com> wrote in message > > news:uT%23stPFXEHA.3632@TK2MSFTNGP10.phx.gbl... > > > Oh Rob, > > > > > > You are trying to mix dynamic SQL and Static SQL. > > > You cannot do that. Your Else condition doesnt have a > > > corresponding IF condition. thats why you are getting the error. > > > > > > To learn more abt dynamic SQL read > > > > > > http://www.sommarskog.se/dynamic_sql.html > > > > > > > > > -- > > > Roji. P. Thomas > > > SQL Server Programmer > > > "rob" <rwc1960@bellsouth.net> wrote in message > > > news:PKzDc.129$O3.27@bignews2.bellsouth.net... > > > > I am doing something wrong in the code below... > > > > > > > > This section fails within the cursor... I am attempting to check for > > > > existence of a table in the database names I am looping through... > > > > > > > > As is, Error is Incorrect syntax near keyword 'ELSE' > > > > If I comment out from BEGIN to the END, Error is Incorrect syntax > near > > > ')' > > > > > > > > Thanks ! > > > > > > > > DECLARE @sql as varchar(8000) > > > > DECLARE @SalespersonDB as varchar(8000) > > > > > > > > set @SalespersonDB = 'EJ' > > > > set @sql = ' if exists (select * from dbo.sysobjects where id = > > > object_id([' > > > > + @SalespersonDB + '].[dbo].[tblCustPrClChanlSlsREP]))' > > > > print @sql > > > > Exec(@sql) > > > > BEGIN > > > > Print 'Do nothing' > > > > END > > > > ELSE > > > > BEGIN > > > > Print 'Do something' > > > > End > > > > > > > > > > > > > > > > > > > >
Hi Roji,
Changed it to the following in order to store the results to a variable... DECLARE @sql as varchar(8000) DECLARE @SalespersonDB as varchar(8000) DECLARE @cnt as nvarchar(10) set @SalespersonDB = 'EJ' set @sql = 'Select @cnt = cast(Count(*) as nvarchar(10)) FROM ' + @SalespersonDB + '.dbo.sysobjects WHERE (xtype = ''U'') and name = ''tblCustPrClChanlSlsREP''' print @sql Exec sp_executesql @sql, N'@cnt nvarchar(10) OUTPUT', @cnt OUTPUT print @cnt Resulted in ERROR.... Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 10 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. [quoted text, click to view] "Roji. P. Thomas" <lazydragon@nowhere.com> wrote in message news:uT%23stPFXEHA.3632@TK2MSFTNGP10.phx.gbl... > Oh Rob, > > You are trying to mix dynamic SQL and Static SQL. > You cannot do that. Your Else condition doesnt have a > corresponding IF condition. thats why you are getting the error. > > To learn more abt dynamic SQL read > > http://www.sommarskog.se/dynamic_sql.html > > > -- > Roji. P. Thomas > SQL Server Programmer > "rob" <rwc1960@bellsouth.net> wrote in message > news:PKzDc.129$O3.27@bignews2.bellsouth.net... > > I am doing something wrong in the code below... > > > > This section fails within the cursor... I am attempting to check for > > existence of a table in the database names I am looping through... > > > > As is, Error is Incorrect syntax near keyword 'ELSE' > > If I comment out from BEGIN to the END, Error is Incorrect syntax near > ')' > > > > Thanks ! > > > > DECLARE @sql as varchar(8000) > > DECLARE @SalespersonDB as varchar(8000) > > > > set @SalespersonDB = 'EJ' > > set @sql = ' if exists (select * from dbo.sysobjects where id = > object_id([' > > + @SalespersonDB + '].[dbo].[tblCustPrClChanlSlsREP]))' > > print @sql > > Exec(@sql) > > BEGIN > > Print 'Do nothing' > > END > > ELSE > > BEGIN > > Print 'Do something' > > End > > > > > >
thats what sql server expects the type of @sql. declare ur @sql variable as:
DECLARE @sql as nvarchar(8000) check out http://www.sommarskog.se/dynamic_sql.html for more info. hope this helps.. [quoted text, click to view] "rob" <rwc1960@bellsouth.net> wrote in message news:f4DDc.1087$JP6.996@bignews3.bellsouth.net... > Hi Roji, > > Changed it to the following in order to store the results to a variable... > > DECLARE @sql as varchar(8000) > DECLARE @SalespersonDB as varchar(8000) > DECLARE @cnt as nvarchar(10) > > set @SalespersonDB = 'EJ' > > set @sql = 'Select @cnt = cast(Count(*) as nvarchar(10)) FROM ' + > @SalespersonDB + '.dbo.sysobjects WHERE (xtype = ''U'') and name = > ''tblCustPrClChanlSlsREP''' > print @sql > Exec sp_executesql @sql, N'@cnt nvarchar(10) OUTPUT', @cnt OUTPUT > print @cnt > > Resulted in ERROR.... > Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 10 > Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. > > > > "Roji. P. Thomas" <lazydragon@nowhere.com> wrote in message > news:uT%23stPFXEHA.3632@TK2MSFTNGP10.phx.gbl... > > Oh Rob, > > > > You are trying to mix dynamic SQL and Static SQL. > > You cannot do that. Your Else condition doesnt have a > > corresponding IF condition. thats why you are getting the error. > > > > To learn more abt dynamic SQL read > > > > http://www.sommarskog.se/dynamic_sql.html > > > > > > -- > > Roji. P. Thomas > > SQL Server Programmer > > "rob" <rwc1960@bellsouth.net> wrote in message > > news:PKzDc.129$O3.27@bignews2.bellsouth.net... > > > I am doing something wrong in the code below... > > > > > > This section fails within the cursor... I am attempting to check for > > > existence of a table in the database names I am looping through... > > > > > > As is, Error is Incorrect syntax near keyword 'ELSE' > > > If I comment out from BEGIN to the END, Error is Incorrect syntax near > > ')' > > > > > > Thanks ! > > > > > > DECLARE @sql as varchar(8000) > > > DECLARE @SalespersonDB as varchar(8000) > > > > > > set @SalespersonDB = 'EJ' > > > set @sql = ' if exists (select * from dbo.sysobjects where id = > > object_id([' > > > + @SalespersonDB + '].[dbo].[tblCustPrClChanlSlsREP]))' > > > print @sql > > > Exec(@sql) > > > BEGIN > > > Print 'Do nothing' > > > END > > > ELSE > > > BEGIN > > > Print 'Do something' > > > End > > > > > > > > > > > >
Oh Rob,
You are trying to mix dynamic SQL and Static SQL. You cannot do that. Your Else condition doesnt have a corresponding IF condition. thats why you are getting the error. To learn more abt dynamic SQL read http://www.sommarskog.se/dynamic_sql.html -- Roji. P. Thomas SQL Server Programmer [quoted text, click to view] "rob" <rwc1960@bellsouth.net> wrote in message news:PKzDc.129$O3.27@bignews2.bellsouth.net... > I am doing something wrong in the code below... > > This section fails within the cursor... I am attempting to check for > existence of a table in the database names I am looping through... > > As is, Error is Incorrect syntax near keyword 'ELSE' > If I comment out from BEGIN to the END, Error is Incorrect syntax near ')' > > Thanks ! > > DECLARE @sql as varchar(8000) > DECLARE @SalespersonDB as varchar(8000) > > set @SalespersonDB = 'EJ' > set @sql = ' if exists (select * from dbo.sysobjects where id = object_id([' > + @SalespersonDB + '].[dbo].[tblCustPrClChanlSlsREP]))' > print @sql > Exec(@sql) > BEGIN > Print 'Do nothing' > END > ELSE > BEGIN > Print 'Do something' > End > >
It appears that you have a separate DB for each sales person. Why? The most
logical design would seem to be to add a salesperson column to your table(s) in a single database. That would avoid the need for all this messy dynamic SQL. -- David Portas SQL Server MVP --
Thanks David,
I have about 20 different salespeople all using the same client tool to access their data via ODBC. They ABOLUTELY cannot view each others data. They will have permissions to their database only. The client tool they will be using will be the same for each salesperson, just different connction info, table names etc. remain the same for each client tool. Obviously, there is a salesperson column in the primary database. [quoted text, click to view] "David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message news:FLedncvYvrUF0ULdRVn-sA@giganews.com... > It appears that you have a separate DB for each sales person. Why? The most > logical design would seem to be to add a salesperson column to your table(s) > in a single database. That would avoid the need for all this messy dynamic > SQL. > > -- > David Portas > SQL Server MVP > -- > >
Thanks again for the response David...
[quoted text, click to view] >>Then allow them to view the database only through SPs that restrict them tojust the data they own. Would it then require separate sp's for each saleperson ? Or would you use a stored parameter in the MSAccess client tool ? Problem is that if a parameter were ever changed, or a client tool set-up error occurred by mistake, and data was viewed by the wrong salesperson, it would cause a complete meltdown. [quoted text, click to view] >>Segmenting databases in the way you have is unnecessary and it just creates a management headache, makes developmentneedlessly complex (your question in this thread for example) and makes it cumbersome to write Decision Support queries (find the top selling salesperson for example). Basically, there is a primary database that feeds each of the salesperson databases, each salesperson updates their MS Access client tool to review data and make forecast changes... the query to find the Top selling saleperson would be easy becasue it would be run against the Primary (feeder) database. Please respond back if you still think this is a bad route.... [quoted text, click to view] "David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message news:7q-dnRoLJ5JftX3dRVn-ug@giganews.com... > Then allow them to view the database only through SPs that restrict them to > just the data they own. Segmenting databases in the way you have is > unnecessary and it just creates a management headache, makes development > needlessly complex (your question in this thread for example) and makes it > cumbersome to write Decision Support queries (find the top selling > salesperson for example). > > -- > David Portas > SQL Server MVP > -- > >
Then allow them to view the database only through SPs that restrict them to
just the data they own. Segmenting databases in the way you have is unnecessary and it just creates a management headache, makes development needlessly complex (your question in this thread for example) and makes it cumbersome to write Decision Support queries (find the top selling salesperson for example). -- David Portas SQL Server MVP --
David, I have been thinking alot about your point of view... is there any other way to provide "row level" permissions ? Other than a client tool parameter, or separate sp's... I figured that by setting up one database per saleperson, 2 mistakes must occur for a salesperson to see another salespersons data 1) the wrong connection parameters would have to be used when setting up the MSAccess client tool, and 2) they would have to incorrectly assign permissions to the wrong database. Thanks [quoted text, click to view] "David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message news:7q-dnRoLJ5JftX3dRVn-ug@giganews.com... > Then allow them to view the database only through SPs that restrict them to > just the data they own. Segmenting databases in the way you have is > unnecessary and it just creates a management headache, makes development > needlessly complex (your question in this thread for example) and makes it > cumbersome to write Decision Support queries (find the top selling > salesperson for example). > > -- > David Portas > SQL Server MVP > -- > >
[quoted text, click to view]
On Tue, 29 Jun 2004 09:47:17 -0400, rob wrote: > >David, > >I have been thinking alot about your point of view... is there any other way >to provide "row level" permissions ? Other than a client tool parameter, >or separate sp's... I figured that by setting up one database per >saleperson, 2 mistakes must occur for a salesperson to see another >salespersons data 1) the wrong connection parameters would have to be used >when setting up the MSAccess client tool, and 2) they would have to >incorrectly assign permissions to the wrong database. > >Thanks Hi Rob, A common way to provide row level permissions is the use of views. From BOL, subject "Using Views as Security Mechanisms": --> start quote By defining different views and granting permissions selectively on them, users, groups, or roles can be restricted to different subsets of data. For example: Access can be restricted to a subset of the rows of a base table. For example, define a view that contains only rows for business and psychology books and keep information about other types of books hidden from users. <-- end quote Best, Hugo --
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |