July 14, 2008 at 7:24 pm
-- When I execute the proc, statement does not reconized the single quotes i.e. Exec Proc 'Joe'
-- I did a count on my TEST table and did not return any results. I know there are atleast 2 'Joe' in my
-- User Table
ALTER PROCEDURE dbo.Proc (@PARAM4 nvarchar )
AS
Create table TEST (id int identity(1,1) , RowNum nvarchar(100))
insert TEST(RowNum)
(Select Count (1)RowNum
From
usertable
Where
table.colname = @PARAM3
July 15, 2008 at 12:17 am
ALTER PROCEDURE dbo.Proc (@PARAM4 nvarchar )
AS
Create table TEST (id int identity(1,1) , RowNum nvarchar(100))
EXEC('insert TEST(RowNum)
(Select Count (1)RowNum
From
usertable
Where
table.colname = '''+@PARAM3+''')')
July 15, 2008 at 6:10 am
What he's saying is, you can't refer to tables by variable names in regular TSQL statements. Instead you have to build a dynamic query as a string, inserting your parameter where appropriate, and then execute that.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 16, 2008 at 2:59 am
xsaycocie (7/14/2008)
ALTER PROCEDURE dbo.Proc (@PARAM4 nvarchar )
There are two things wrong here. First, the procedure's name is "proc" which is not going to be allowed. However, I assume that you named it that just to be illustrative. The second problem, and this is what is causing your headache, is that you define the parameter as just "nvarchar" rather than "nvarchar(25)" or some other length. Though you will invoke the procedure with the value 'Joe', all the procedure will see is 'J'. That is, "nvarchar" is interpreted as "nvarchar(1)".
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply