473,320 Members | 2,177 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

select statement returns null in stored proc

If I run this statement in Query Analyzer, it properly returns 1
for my testing table. But if I put the statement into a stored
procedure, the stored procedure returns NULL. What am I doing
wrong? I suspect it may be related to how I defined the parameters
for the stored procedure. Perhaps my definition of TableName and
ColumnName don't match what COLUMNPROPERTY and OBJECT_ID expect to
receive, but I don't know where to look for the function declarations
for those. Any pointers would be appreciated.

Select statement:
SELECT COLUMNPROPERTY(OBJECT_ID('Table1'), 'TestID', 'IsIdentity') AS
IsIdentity

Table definition:
CREATE TABLE [dbo].[Table1] (
[TestID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

Stored Procedure definition:
CREATE PROCEDURE spTest
(@TableName varchar,
@ColumnName varchar)
AS SELECT COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName,
'IsIdentity') AS IsIdentity
Feb 22 '06 #1
2 3034
You need to specify lengths for your varchar parameters

CREATE PROCEDURE spTest
(@TableName varchar(50),
@ColumnName varchar(50))

Feb 22 '06 #2
ma******@hotmail.com wrote:
You need to specify lengths for your varchar parameters

CREATE PROCEDURE spTest
(@TableName varchar(50),
@ColumnName varchar(50))


That was it. Thanks.
Feb 22 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Jan | last post by:
I store sql-commands in a database table. In the first step I get the sql command out of the database table with embedded sql. In the second step I try to execute the command, which i got from the...
6
by: Samuel Hon | last post by:
Hi I'm not sure what the best approach for this is: I have a stored procedure which I would like to use to return several output values instead of returning a recordset. CREATE PROCEDURE...
10
by: serge | last post by:
Using "SELECT * " is a bad practice even when using a VIEW instead of a table? I have some stored procedures that are identical with the difference of one statement in the WHERE clause. If I...
4
by: Andrew Baker | last post by:
I have the following code that calles a stored proc in SQLServer. When the output parameter @custref is null (System.DBNull) I cant seem to find a test for this and I get an exception. I know I...
7
by: ja | last post by:
Hello, I have a table that contains approx 2 million records. I want to query 2,000 random records. Example: 500,000 names divided by 20,000 samples = 25. The N is 25, meaning samples would...
19
by: natG | last post by:
On a warehouse app, our Java clients constantly load/insert rows into the db. I would like to throttle these inserts (1.5 million rows per hr) from the Java app, based on current 'busy state' of...
2
by: SFM | last post by:
I just want a simple datareader, that i can read the value returned from a select statement executed on a SQL server 2005 db. The code below should work in, but email= rdr.ToString(); when i...
1
by: codedhacker | last post by:
I have a piece of code that uses the db-library with sql server 2000/2005 and runs the following delete statement: DELETE FROM TABLE1 WHERE COL1 IN( 'Some Val1' ) AND COL2 IN( 'Some Val2' )...
3
by: Deane | last post by:
I have a stored proc that returns a resultset. I would like to deposit that resultset into a table. Kind of like a "SELECT INTO", but using a stored proc. If I could do this -- SELECT INTO...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.