Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sica
Posting Yak Master
143 Posts |
 Posted - 2001-04-05 : 19:21:20
|
| Hi,I have a sp that exports via BCP a table to a text file taking in a parameter with a table name.I want if the table does't contain any rows to export the table to an xls file instead of a text file.My question is how to check how many rows the table contains and how to put the count result from the table into a variable to be able to make the decision: xls or txt.My solution is to make another sp and send in a parameter with the table name to be exported,use a working table(can't use a temporary table) where I put the value of rows counting of the table and send the information back to the main procedure as an output parameter. --create the working tableCREATE TABLE tempus (idcheck INT)--create spCREATE PROCEDURE EmptyCheck @tablename VARCHAR(20),@check INT OUTPUTASSET NOCOUNT ONDECLARE @SQL VARCHAR(500)SET @SQL = ' IF (SELECT COUNT(*) FROM '+@tablename+')>0 INSERT INTO tempus SELECT 1' EXECUTE (@SQL)SELECT @check = idcheck FROM tempusTRUNCATE TABLE tempusGO--the main spCREATE PROCEDURE main @DatabaseName VARCHAR(20),@TableName VARCHAR(20)ASSET NOCOUNT ONDECLARE @a INTEXECUTE EmptyCheck @TableName,@a OUTPUTIF @a >0 EXECUTE('master..xp_cmdshell "bcp '+ @DatabaseName +'.dbo.'+ @TableName +' out C:\file.txt -c -Usa -P -SWIN2000"')ELSE EXECUTE ('master..xp_cmdshell "bcp '+ @DatabaseName +'.dbo.'+ @TableName +' out C:\file.xls -c -Usa -P -SWIN2000"')GO--call the sp from the main spEXEC main 'pubs','Address' If Address table contains nothing it should be created an xls file...I know there must be a easier way...So every suggestion would be appreciated.Thanks! |
|
|
|
|
|
Dynamic Statements .Any suggestions...!!!