December 15, 2014 at 11:04 am
How do I pass a varible to an INT calculation?
CREATE PROC CLEAR_MY_TABLE
@TableStat varchar(30)
AS
If OBJECT_ID('MyDB.dbo.' + @TableStat + '') is not null
BEGIN
---PRINT 'I FOUND THE TABLE'
DECLARE @count INT = -1;
SELECT @count = (Select COUNT(*) FROM [dbo]. + @TableStat);
IF (@count > 0)
BEGIN
TRUNCATE TABLE @TABLESTAT
END
December 15, 2014 at 11:23 am
Why do you want to count before truncation? That would only slow you down. Additionally to that, you can't use variable values as table names unless you use dynamic SQL. Same goes for other statements.
Here's an alternative:
CREATE PROC CLEAR_MY_TABLE
@TableStat nvarchar(128)
AS
DECLARE @SQL nvarchar(200);
SELECT @SQL = 'TRUNCATE TABLE ' + QUOTENAME(@TABLESTAT);
IF OBJECT_ID( QUOTENAME(@TABLESTAT) ) is not null
EXEC( @SQL );
December 16, 2014 at 3:45 pm
I'm still getting an error. I added the truncate command just to give an idea of what I was trying to do.
"Conversion failed when converting the nvarchar value 'Select COUNT(*) FROM [AdventureWorks2014].[Production].[Product]"
Declare @count int = -1
Declare @TableStat NVARCHAR(128)
SET @TableSTAT = 'Product'
/* Verify the user typed the table name correctly and it exist in the database */
If OBJECT_ID('AdventureWorks2014.Production.' + @TableStat + '') is not null
BEGIN
/* Now we know the table exist. Does it have any data? */
/* If no data exist exit query */
PRINT 'I FOUND A VALID TABLE WITH THE NAME SUPPLIED'
--DECLARE @count NVARCHAR(128)
DECLARE @params NVARCHAR(MAX)
Set @count = N'Select COUNT(*) FROM [AdventureWorks2014].[Production].' + QUOTENAME(@TableStat)
EXEC (@count)
If ((@count) > 0)
BEGIN
PRINT 'I FOUND THE TABLE AND IT HAD MORE THAN 1 ROW'
END
END
ELSE BEGIN
PRINT '* * * THE TABLE NAME SUPPLIED IS NOT VALID. PLEASE CHECK THE SPELLING * * *'
END
December 16, 2014 at 3:53 pm
You can't do: SELECT @count = (Select COUNT(*) FROM [dbo]. + @TableStat);
You need to build a string that has the query you want to execute, then execute that string.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 16, 2014 at 3:56 pm
Your variable is declared as integer and you're assigning a string value. Read about sp_executesql to understand how to use parametrized dynamic queries. Using parametrized queries, you can use an output parameter that gets populated in your dynamic code.
December 16, 2014 at 3:56 pm
You should read this article about dynamic sql. http://www.sommarskog.se/dynamic_sql.html
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 16, 2014 at 4:18 pm
Two side notes:
1. A table with one row will return the message that has more than 1 row. :w00t:
2. If you don't care about the number of rows in the table and just want something like true or false, you can use a faster approach.(I'm not sure that the table is correct)
DECLARE @C int
SELECT @C = 1 WHERE EXISTS(SELECT * FROM [AdventureWorks2014].[Production].[Product])
December 16, 2014 at 4:41 pm
Might as well handle any db and any schema, but defaulting to your main one.
CREATE PROC CLEAR_MY_TABLE
@TableStat varchar(100)
AS
SET NOCOUNT ON;
DECLARE @TableStat varchar(100)
SET @TableStat = ISNULL(PARSENAME(@TableStat, 3), 'MyDB') + '.' + ISNULL(PARSENAME(@TableStat, 2), 'dbo') + '.' + PARSENAME(@TableStat, 1)
IF OBJECT_ID(@TableStat) IS NOT NULL
BEGIN
---PRINT 'I FOUND THE TABLE'
DECLARE @row_found tinyint
DECLARE @sqln nvarchar(4000)
SET @sqln = 'SELECT @row_found = CASE WHEN EXISTS(SELECT TOP (1) * FROM ' + @TableStat + ') THEN 1 ELSE 0 END'
EXEC sp_executesql @sqln, N'@row_found tinyint OUTPUT', @row_found OUTPUT
IF @row_found > 0
BEGIN
---PRINT 'THE TABLE HAS AT LEAST ONE ROW!'
SET @sqln = 'TRUNCATE TABLE ' + @TableStat
END --IF
END --IF
GO
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 17, 2014 at 7:49 pm
Thanks every one especially SSCrazy, that was perfect. Now I can reverse engineer it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply