April 7, 2008 at 12:14 pm
Im a certified newbee to sqlserver's & its various features.
I was trying to run a stored proc that I just wrote in here & it is throwing me syntax errors & they are -
Server: Msg 102, Level 15, State 1, Procedure CompareTables, Line 4
Incorrect syntax near '('.
Server: Msg 102, Level 15, State 1, Procedure CompareTables, Line 8
Incorrect syntax near 'nvarchar'.
CREATE PROCEDURE
CompareTables(
@filefit nvarchar(100),
@(select bb.Vraiid, pc.FLCA
from blueribbon bb
join pcvar pc on pc.Bbgid = bb.Bbgid
join btiy be on be.Bbgid = bb.Bbgid
where pc.FLCA !=0 or pc.Slca !=0 or pc.Tlca != 0) nvarchar(100),
@id, lev1 nvarchar(1000), @Vraiid, FLCA nvarchar(1000) = '')
AS declare @sql nvarchar(8000)
Apart from this, if you find anything wrong with the above lines, please feel free to shout out !
April 7, 2008 at 12:23 pm
I may be missing something.
The part after the name of the proc "CompareTables", in parentheses, is a spot to declare input and/or output parameters.
The first one, "@filefit nvarchar(100)", is fine. That means you want to have an input parameter called @filefit which can hold up to 100 extended-set characters. No problem. Follow you that far.
The second part:
"@(select bb.Vraiid, pc.FLCA
from blueribbon bb
join pcvar pc on pc.Bbgid = bb.Bbgid
join btiy be on be.Bbgid = bb.Bbgid
where pc.FLCA !=0 or pc.Slca !=0 or pc.Tlca != 0) nvarchar(100),
@id, lev1 nvarchar(1000), @Vraiid, FLCA nvarchar(1000) = ''
doesn't look like standard T-SQL to me. I'm not sure what it's meant to do. Are you trying to declare a table variable?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2008 at 12:26 pm
Gsquared -
Im trying to declare another table, which happens to be the output of
select bb.Vraiid, pc.FLCA
from blueribbon bb
join pcvar pc on pc.Bbgid = bb.Bbgid
join btiy be on be.Bbgid = bb.Bbgid
where pc.FLCA !=0 or pc.Slca !=0 or pc.Tlca != 0
Hope I make sense here.
April 7, 2008 at 12:34 pm
Ah. That should go in the body of the proc. After "as". Not as part of the input/output parameters.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2008 at 12:43 pm
Gsquared -
Some one gave me this template & wants me to frame my comparision using this. Now the issue that I have is, I can directly refer the first table but I cant do that with the second one & that is a combination of 2 columns from 2 diff tables.
The first table is filefit(id,lev1) & the second table is combination of bb.Vraiid, pc.FLCA from Pcvar & btiy tables.
So how do I do this ?
CREATE PROCEDURE CompareTables(@table1 varchar(100), @table2 Varchar(100), @T1ColumnList varchar(1000),@T2ColumnList varchar(1000) = '')AS
declare @sql varchar(8000);
IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnListset @sql = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList + ' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' + @t2ColumnList + ' FROM ' + @Table2set @sql = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList + ' FROM (' + @sql + ') A GROUP BY ' + @t1ColumnList + ' HAVING COUNT(*) = 1'
exec ( @sql)
April 7, 2008 at 12:44 pm
pommguest99:
the bold areas below are incorrect.
CREATE PROCEDURE
CompareTables(
@filefit nvarchar(100),
@(select bb.Vraiid, pc.FLCA
from blueribbon bb
join pcvar pc on pc.Bbgid = bb.Bbgid
join btiy be on be.Bbgid = bb.Bbgid
where pc.FLCA !=0 or pc.Slca !=0 or pc.Tlca != 0) nvarchar(100),
@id, lev1 nvarchar(1000), @Vraiid, FLCA nvarchar(1000) = '')
AS declare @sql nvarchar(8000)
you can't pass tables to a stored procedure. you can either create a temp table or table variable in the body of the stored proc.
declare @myTable TABLE( Vraiid datatype [nullability], FLCA datatype [nullabilty] )
INSERT INTO @myTable (Vraiid, FLCA)
select bb.Vraiid, pc.FLCA
from blueribbon bb
join pcvar pc on pc.Bbgid = bb.Bbgid
join btiy be on be.Bbgid = bb.Bbgid
where pc.FLCA !=0 or pc.Slca !=0 or pc.Tlca != 0
or
select bb.Vraiid, pc.FLCA
into #myTable
from blueribbon bb
join pcvar pc on pc.Bbgid = bb.Bbgid
join btiy be on be.Bbgid = bb.Bbgid
where pc.FLCA !=0 or pc.Slca !=0 or pc.Tlca != 0
also, @id and @Vraiid are missing their datatype; lev1 and FLCA need a leading @ to declare them as parameters.
April 7, 2008 at 1:58 pm
Gsquared / Antonio,
Thanks for being patient wiht me.
Here is what I came up & it is throwing a syntax error. My another question is how do I run this stored proc as I believe Im at the tail end of figuring this out.
Thank you.
CREATE PROCEDURE NCompareTables(@filefit varchar(100),
@myTable Varchar(100),
@id int , @lev1 int,
@Vraiid int, @FLCA int)AS
declare @sql varchar(8000);
If @Vraiid = '' set @Vraiid = @id
Set @sql = 'select '''+@filefit+'''As Firsttable,'+@id+@lev1+'FROM'+
@mytable+ 'Union all select '''+
@filefit+''' As Secondtable,'+@Vraiid+@FLCA+'FROM'+ @myTable
set @sql = 'select Max(TableName) as TableName3,
''+@id+@lev1+'FROM(' + @sql + ') A GROUP BY ' + @id+@lev1+' HAVING COUNT(*) = 1'
exec(@SQL)
April 7, 2008 at 2:12 pm
It looks like there's an extra single-quote at:
''+@id
I copy-and-pasted your code into Management Studio, removed that single-quote and it didn't give a syntax error when I ran it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2008 at 2:20 pm
Gsquared -
Yes, you are right in that I missed one ' mark in there.
But the issue is what I intended to create & what I created are two different things here. I want to create a proc that would compare two tables & show the similarities as well as the differences. So someone at work gave me this template & I worked on it assuming that it is going to bring in what I need & now Im not sure.
does this do that as it is asking me all kinds of parameters & what I intended to give was just the two table names & the four column values that needed to be compared in here & they are
1. id, lev1 from filefit
2. This is bit tricky as it is bb.Vraiid, pc.FLCA from a join condition
I hope I havent wasted much of my day in this.
Thanks a bunch for your help !
April 7, 2008 at 2:23 pm
Not sure what you mean by comparing two tables for similarities and differences. Do you mean compare the data in two tables, or the column names, or something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2008 at 2:28 pm
Yes, comparing data b/w Filefit(table1) and from a temp table which holds join data & that would be myTempTable(Vraiid & FlCA)
Tables - Filefit, myTemptable(which happens to be a join here)
columns in filefit - Id, Lev1
Columns in mytemptable Vraiid & Flca.
I need to pull out the differences as well as similarities between the above 4 columns and the two tables can be joined on ID = Vraiid
April 7, 2008 at 2:32 pm
pommguest99 (4/7/2008)
Gsquared / Antonio,Thanks for being patient wiht me.
Here is what I came up & it is throwing a syntax error. My another question is how do I run this stored proc as I believe Im at the tail end of figuring this out.
Thank you.
CREATE PROCEDURE NCompareTables(@filefit varchar(100),
@myTable Varchar(100),
@id int , @lev1 int,
@Vraiid int, @FLCA int)AS
declare @sql varchar(8000);
If @Vraiid = '' set @Vraiid = @id
Set @sql = 'select '''+@filefit+'''As Firsttable,'+@id+@lev1+'FROM'+
@mytable+ 'Union all select '''+
@filefit+''' As Secondtable,'+@Vraiid+@FLCA+'FROM'+ @myTable
set @sql = 'select Max(TableName) as TableName3, '
+@id+@lev1+'FROM(' + @sql + ') A GROUP BY ' + @id+@lev1+' HAVING COUNT(*) = 1'
exec(@SQL)
i'm sorry, but the dynamically generated statement doesn't make sense even after fixing the double quote issue.
if @Vraidd = '' set @Vraiid = @id
@Vraidd is an integer but you're comparing it to an empty string so it is implicitly converting '' to 0. is that what you want/expect or is
[font="Courier New"]set @Vraidd = isnull(@Vraidd, @id)[/font]
more appropriate?
'select '''+@filefit+'''As Firsttable,'+@id+@lev1+'FROM'
this attempts to add two integers (@id +@lev1) to a string. that portion of the statement will cause an error when executed unless you cast/convert @id and @lev1 to strings. Also, the generated SELECT never identifies any columns... it only has literals so there's no point to specifying a table:
[font="Courier New"]SELECT 'literal' as Firsttable, @id, @lev1 FROM myTable[/font]
What's the final SELECT supposed to look like and what's its purpose?
April 7, 2008 at 2:38 pm
pommguest99 (4/7/2008)
Yes, comparing data b/w Filefit(table1) and from a temp table which holds join data & that would be myTempTable(Vraiid & FlCA)Tables - Filefit, myTemptable(which happens to be a join here)
columns in filefit - Id, Lev1
Columns in mytemptable Vraiid & Flca.
I need to pull out the differences as well as similarities between the above 4 columns and the two tables can be joined on ID = Vraiid
Not sure what "similarities" you'd be working with, but it sounds like the basic idea is:
select *
from filefit
inner join #myTemptable
on filefit.id = #mytemptable.vraiid
where lev1 != flca
Would find all the rows in both tables where id = vraiid, but where lev1 does not equal flca. Is that what you're looking for?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2008 at 2:42 pm
Gsquared / Antonio,
1.
I guess I have to go with the following as this what I intend to do.
set @Vraidd = isnull(@Vraidd, @id)
I will sit & figure out the rest of the issues that you both have mentioned in here.
Thanks a bunch for your time !
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply