Making a query using EXEC - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Making a query using EXEC

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-01-01 : 13:34:22

DECLARE @table_name varchar(20)
SET @table_name = 'Articles'

SET @str = 'SELECT COUNT(*) FROM information_schema.columns WHERE
table_name = ' + @table_name
Print @str

EXEC (@str)

Every time I execute this I get the error saying column Articles is not defined.

Mohammad Azam
www.azamsharp.net

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-01 : 13:52:19

The table name should be sorrounded by single quotes, when the query is created.
U should have seen that, when u get the output from Print @str

SET @str = 'SELECT COUNT(*) FROM information_schema.columns WHERE
table_name = ''' + @table_name + ''''

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-01-01 : 14:04:23

Thanks a million :D

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-01-01 : 14:22:50

How can I assign the COUNT(*) to a variable

DECLARE @noOfRecords int

SET @str = 'SELECT @noOfRecords = COUNT(*) FROM information_schema.columns WHERE
table_name = ''' + @table_name + ''''

the above statement does not work since it goes out of state.

Any ideas!

Mohammad Azam
www.azamsharp.net

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-01 : 14:55:06

SET @str = 'SELECT COUNT(*) as [My Variable] FROM information_schema.columns WHERE
table_name = ''' + @table_name + ''''

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-01-01 : 15:08:33

I am trying this but it does not move the total number of rows in the @totalRows variable

SET @SQLString = 'SELECT COUNT(*) AS [@totalRows] FROM information_schema.columns WHERE
table_name = ''' + @table_name + ''''

--Print @SQLString
EXEC(@SQLString)

WHILE @counter <= @totalRows -- @totalRows is nothing

Mohammad Azam
www.azamsharp.net

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-01 : 15:50:57

I don't have a quick solution. I'll suggest u a solution may be innefficient.
Write the query in a UDF or a Stored procedure and get the return result to ur WHILE loop

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-01-01 : 16:30:11

if i remember correctly this is one way:


declare @cnt int
declare @sql nvarcahr(4000)
select  @sql = 'select @cnt = count(*) from ' + @tableName + ' where ...'
exec    sp_executeSql @sql, N'@cnt int out', @cnt out
select  @cnt

Go with the flow & have fun! Else fight the flow

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-01-01 : 16:43:28

Yes, it worked thanks. I still need a little more help :)

Here is my total stored procedure:

CREATE PROCEDURE dbo.sp_test

@table_name varchar(20)

AS

-- declare the variables
DECLARE @column_name varchar(20)
DECLARE @ordinal_position int
DECLARE @counter int
DECLARE @totalRows int
DECLARE @commandObjectName varchar(20)
DECLARE @SQLString nvarchar(4000)

SET @counter = 1

SET @commandObjectName = 'myCommand'

SET @SQLString = 'SELECT @totalRows = COUNT(*) FROM information_schema.columns WHERE table_name = ''' + @table_name + ''''

EXEC sp_executeSql @SQLString, N'@totalRows int out', @totalRows out

Print @totalRows

--SELECT @totalRows = COUNT(*) FROM information_schema.columns WHERE
--table_name = '' + @table_name + ''

WHILE @counter <= @totalRows
BEGIN

SELECT @column_name = COLUMN_NAME FROM information_schema.columns WHERE
table_name = @table_name AND @counter = ORDINAL_POSITION

SELECT @ordinal_Position = ORDINAL_POSITION FROM information_schema.columns WHERE table_name = 'Categories'
Print @ordinal_Position

Print @commandObjectName+'.Parameters.AddWithValue("@'+@column_name+'",'+LOWER(@column_name)+')'

SET @counter = @counter + 1

END

SET @totalRows = 0
GO

The bold lines never gets executed any reason.

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-01-01 : 16:56:59

I think for some reason the ORDINAL_POSITION is returning nothing:

SELECT @ordinal_position = ORDINAL_POSITION FROM information_schema.columns WHERE table_name = 'Products'
Print @ordinal_position // prints nothing

Mohammad Azam
www.azamsharp.net

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-01-01 : 17:02:53

would you mind telling me what do you want to do with this code??

Go with the flow & have fun! Else fight the flow

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-01-01 : 17:08:55

Well, what I am trying to do is to generating the string something like this:
The following is the C# code generated. The T-SQL query works fine when I dont put it inside the STORED PROCEDURE but when I put inside SPROC whole thing crashes.

myCommand.Parameters.AddWithValue("@CustomerID",customerid)
myCommand.Parameters.AddWithValue("@CompanyName",companyname)
myCommand.Parameters.AddWithValue("@ContactName",contactname)
myCommand.Parameters.AddWithValue("@ContactTitle",contacttitle)
myCommand.Parameters.AddWithValue("@Address",address)
myCommand.Parameters.AddWithValue("@City",city)
myCommand.Parameters.AddWithValue("@Region",region)
myCommand.Parameters.AddWithValue("@PostalCode",postalcode)
myCommand.Parameters.AddWithValue("@Country",country)
myCommand.Parameters.AddWithValue("@Phone",phone)
myCommand.Parameters.AddWithValue("@Fax",fax)

The while loop will run and check for the ordinal position and when I get ordinal = 1 this means that this is the column1 and so on.

Mohammad Azam
www.azamsharp.net

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-01-01 : 17:31:27

for this you don't need an ordinal position at all.
adding parameters is not ordinal position dependant.

Go with the flow & have fun! Else fight the flow

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-01-01 : 17:52:03

Even if I remove the ordinal position it does not work.

the following code @column_name is returning null everytime. Why is that?

SELECT @column_name = COLUMN_NAME FROM information_schema.columns WHERE
table_name = 'Customer'

Mohammad Azam
www.azamsharp.net

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-01-02 : 09:48:50

this query will return the last record in the returned resultset.

SELECT @column_name = COLUMN_NAME FROM information_schema.columns WHERE
table_name = 'Customer'

if it returns null then i guess you don't have a table named 'customer'

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-02 : 09:55:17

quote:
Originally posted by azamsharp

Well, what I am trying to do is to generating the string something like this:
The following is the C# code generated. The T-SQL query works fine when I dont put it inside the STORED PROCEDURE but when I put inside SPROC whole thing crashes.

myCommand.Parameters.AddWithValue("@CustomerID",customerid)
myCommand.Parameters.AddWithValue("@CompanyName",companyname)
myCommand.Parameters.AddWithValue("@ContactName",contactname)
myCommand.Parameters.AddWithValue("@ContactTitle",contacttitle)
myCommand.Parameters.AddWithValue("@Address",address)
myCommand.Parameters.AddWithValue("@City",city)
myCommand.Parameters.AddWithValue("@Region",region)
myCommand.Parameters.AddWithValue("@PostalCode",postalcode)
myCommand.Parameters.AddWithValue("@Country",country)
myCommand.Parameters.AddWithValue("@Phone",phone)
myCommand.Parameters.AddWithValue("@Fax",fax)

The while loop will run and check for the ordinal position and when I get ordinal = 1 this means that this is the column1 and so on.

Mohammad Azam
www.azamsharp.net

Try this

Select 'myCommand.Parameters.AddWithValue("@'+column_name+'",'+column_name+')' from information_schema.columns
where table_name='yourTable'

Madhivanan

Failing to plan is Planning to fail

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-01-02 : 12:37:06

Thanks for the reply. I used the following code:

Select @GenCode = 'myCommand.Parameters.AddWithValue("@'+column_name+'",'+column_name+')' from information_schema.columns
where table_name='Customer'

Print @GenCode // This prints out NOTHING

Not sure why its not printing anything.

Mohammad Azam
www.azamsharp.net

Kristen
Test

22859 Posts

Posted - 2006-01-02 : 12:50:25

"Not sure why its not printing anything"

Looks like you don't have a table called "Customer".

Are you attached to the correct database?

Is it a case sensitive database? (presuambly not as you are able to use lowercase object names for the system objects)

This will give you a list of the tables:

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY TABLE_NAME

Kristen

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-01-02 : 15:09:58

quote:
Originally posted by Kristen

"Not sure why its not printing anything"

Looks like you don't have a table called "Customer".

Are you attached to the correct database?

Is it a case sensitive database? (presuambly not as you are able to use lowercase object names for the system objects)

This will give you a list of the tables:

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY TABLE_NAME

Kristen

Thanks for the reply. But I know I have a table called Customer. I can query it and it resides in the MyDatabase. My sp_Test proc is a system proc hence I can execute it from anywhere right.

Here is my complete PROC:

CREATE PROCEDURE dbo.sp_test

@table_name nvarchar(20)

AS

-- declare the variables
DECLARE @column_name nvarchar(20)
DECLARE @ordinal_position int
DECLARE @counter int
DECLARE @totalRows int
DECLARE @commandObjectName varchar(20)
DECLARE @SQLString nvarchar(4000)

DECLARE @GenCode nvarchar(4000)

SET @counter = 1

SET @commandObjectName = 'myCommand'

SET @SQLString = 'SELECT @totalRows = COUNT(*) FROM information_schema.columns WHERE table_name = ''' + @table_name + ''''

EXEC sp_executeSql @SQLString, N'@totalRows int out', @totalRows out

Print @totalRows // It prints the rows. this is correct

WHILE @counter <= @totalRows

BEGIN

Select @GenCode = 'myCommand.Parameters.AddWithValue("@'+column_name+'",'+column_name+')' from information_schema.columns
where table_name='Customer'
-- Customer table is in MyDatabase when I --execite this proc first I --type:

--USE MyDatabase
--EXEC sp_Test 'Customer'

Print @GenCode // This print nothing ??

SET @counter = @counter + 1

END

SET @totalRows = 0
GO

Mohammad Azam
www.azamsharp.net

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-01-02 : 17:13:52

no you don't execute it anywhere.
information_schema views are db dependant, there isn't just one main information_schema view.
it's depentant on the db you're in.

Go with the flow & have fun! Else fight the flow

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-01-02 : 23:04:26

But I am in the database that contains 'Customer' table even then it does not return anything.

Mohammad Azam
www.azamsharp.net

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources