November 20, 2003 at 5:02 am
How can you run queries in batches inside stored procedure since you can not use the GO batch command available in QA in a stored procedure. I have tried the begin and end but does not seem to work.
Thanks.
November 20, 2003 at 5:07 am
Take out the GO separator:
create procedure batch_queries as
select *
from table_a
select *
from table_b
select *
from table_c
Jeremy
November 20, 2003 at 5:56 am
The problem I am trying to resolve is that when I run:
==============================
alter table customers add column sname varchar(50)
====================================
in the stored procedure, if I later run a select that includes the sname, i get an error of invalid column name. The query runs in QA when the GO is included as batch separator.
It appears as if the add column is not executed before the select statement because they are all in the same stored procedure.
Any ideas?
November 20, 2003 at 6:23 am
create proc customersproc as
exec ('alter table customers add sname varchar(50)')
exec ('select sname from customers')
go
Works but would not advocate it.
Far away is close at hand in the images of elsewhere.
Anon.
November 20, 2003 at 6:27 am
What are your reservations? i need to know before doing this to a production system.
Thanks
November 20, 2003 at 6:37 am
Why do it?
What is it about the system that requires you to do it?
The proc can only be run once unless you are dropping and creating the table each time!! If so why are you not creating the table with all columns in the first place.
If someone else looked at the table and did not know about your proc how would they know about your new column?
Other than that just seems bad practice to me.
Far away is close at hand in the images of elsewhere.
Anon.
November 20, 2003 at 7:46 am
Thanks. I inherited a script which I decided to put into a stored proc so it can be called from a scheduled job.
At the end of the script, the original writer needed to return all column values except one. Instead of using a select with a long list of columns, he chose to drop the one column not required before using select * .If I can find a way around this then it will be unnecessary to drop and re-create the table.
November 20, 2003 at 7:54 am
quote:
At the end of the script, the original writer needed to return all column values except one.
SELECT *
INTO #temp
FROM tablename
ALTER TABLE #temp DROP COLUMN unwantedcolumn
SELECT * FROM #temp
DROP TABLE #temp
Far away is close at hand in the images of elsewhere.
Anon.
November 20, 2003 at 8:16 am
Thanks. Will consider that.
November 20, 2003 at 9:46 am
why do we have to go arround so much when you don't want that column, just do
select a, b, c , d from tablez
instead of
select * from tablez?
It will works perfectly fine. The 1st query is actually what we all should do anyway. The 2nd query is our lazy way of doing things.
mom
November 21, 2003 at 3:52 am
That won't be a good idea if you have 25 columns in the table and you need 24 of them selected. I will rather drop the column I do not need and use select *.
November 21, 2003 at 4:51 am
"That won't be a good idea if you have 25 columns in the table and you need 24 of them selected. I will rather drop the column I do not need and use select *."
You make your decisions about how to code, but best practice would be to return the named list rather than dropping a column for performance reasons. DDL is expensive; dropping a column (particularly if that column is indexed) will chew up CPU cycles.
If you're lazy like me, the Query Analyzer in SQL 2000 offers you the ability to right click on a table and generate a SELECT statement (complete with column names); I use this for tables with more than 20 columns and cut and paste away.
Hope that helps,
Stu
Edited by - sainswor99 on 11/21/2003 06:20:08 AM
Edited by - sainswor99 on 11/21/2003 10:46:37 AM
November 21, 2003 at 7:03 am
Not only is 'Select *' not part of the Microsodt best practices, they actually recommend using tablename.fieldname through the entire select statement.
It's one of the recommendations that is listed in the new Microsoft SQL Server 2000 Best Practices Analyzer Beta.
http://www.microsoft.com/downloads/details.aspx?FamilyId=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en%5B/url%5D
Matt,
Peace Out!
Regards,
Matt
November 21, 2003 at 8:35 am
To ease coding it:
select '[' + object_name(id) + '].[' + name + '] ,'
from syscolumns
where id = object_id('tablename')
order by colid
Copy and paste the result, remove the columns you don't want. Takes just slightly longer than using select *, and has the advantage of being modifiable so you can use it to speed up writing the select statements for joins too.
I have used this as a way to generate scripts to convert between one table and another; it's especially useful if you're converting between a staging table that has all varchar field, to a real table with other datatypes, like datetime, int, money, etc... and also want to avoid overflowing the varchar fields. To do that requires joining in the systypes table, but you can do some pretty neat stuff if you do.
Matthew Galbraith
ps: the above code is ripe to be parameterized and dropped into an sp as part of your DBA/Programmer's toolkit
November 21, 2003 at 9:02 am
I have a habit of avoiding table names. I always specify my fields but use the alias i give to the table. Just personal preference - it's easier for me to re-read and update.
select
a.Field1
,b.Fieldx
from
TableWithReallyLongName1 as a
inner join TableWithReallyLongName2 as b
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply