Easy SQL query question

Hi,
I have a table (PartNumberTbl) includes all the part number.
I want to add a part number 'ALL' in the following output so that user
can select "ALL" in the DropDownList control:
SELECT DISTINCT (PartNumber) FROM PartNumberTbl WHERE Customer =
@.Customer
I don't want to create a Part Number "ALL" for each customer.
How to do it without create a temp table?
Or is there a better way to handle this?
Thanks,
BenConsider adding it to the client code so that the general query without part
number 'ALL' can be reused by several other applications. If all
applications require this entry as one of the rows, then consider using the
UNION operator.
Anith|||You can handle this at the client side of your application,
or, you can issue the following SELECT statement.
(Suppose that PartNumber is a char column.)
SELECT PartNumber
FROM PartNumberTbl
WHERE Customer = @.Customer
UNION
SELECT 'ALL' as PartNumber
Martin C K Poon
Senior Analyst Programmer
====================================
"Ben" <wubin_98@.yahoo.com> ?
news:1150146922.304849.52960@.c74g2000cwc.googlegroups.com ?...
> Hi,
> I have a table (PartNumberTbl) includes all the part number.
> I want to add a part number 'ALL' in the following output so that user
> can select "ALL" in the DropDownList control:
> SELECT DISTINCT (PartNumber) FROM PartNumberTbl WHERE Customer =
> @.Customer
> I don't want to create a Part Number "ALL" for each customer.
> How to do it without create a temp table?
> Or is there a better way to handle this?
> Thanks,
> Ben
>

Easy question

How can I output the result of a query in a text file.
(I want to use SP)EXEC master..xp_cmdshell 'bcp...

Dynamic SQL with output values

Hi:
How can I in SQL Server 2000 (using Transact SQL) execute a dynamic sql string and at the same time retrieve output params ?
ThanksPlease give a specific example. What are the output parameters ? Are they based on the dynamic sql ? Where are you retrieving the output parameters from ?|||Originally posted by rnealejr
Please give a specific example. What are the output parameters ? Are they based on the dynamic sql ? Where are you retrieving the output parameters from ?

Here's an simple example:

SELECT @.num_records = COUNT(*)
FROM @.TableName

dynamic SQL variable with Output ?

I know this has been dealt with a lot, but I would still really
appreciate help. Thanks.

I am trying to transform this table
YY--ID-Code-R1-R2-R3-R4...R40
2004-1-101--1--2-3-4
2004-2-101--2--3-4-2
...
2005-99-103-4-3-2-1

Into a table where the new columns are the count for 4-3-2-1 for every
distinct code in the first table based on year. I will get the year
from the user-end(Access). I will then create my report based on the
info in the new table. Here's what I've tried so far (only for 1st
column):

CREATE PROCEDURE comptabilisationDYN
@.colonne varchar(3) '*receives R1, then R2, loop is in vba access*
AS

DECLARE @.SQLStatement varchar(8000)
DECLARE @.TotalNum4 int
DECLARE @.TotalNum3 int
DECLARE @.TotalNum2 int
DECLARE @.TotalNum1 int

SELECT SQLStatement = 'SELECT COUNT(*) FROM
dbo.Tbl_Rponses_tudiants WHERE' + @.colonne + '=4 AND YY = @.year'
EXEC sp_executesql @.SQLStatement, N'@.TotalNum4 int OUTPUT', @.TotalNum4
OUTPUT

INSERT INTO Comptabilisation(Total4) VALUES (@.TotalNum4)
GOPatrik (patrik.maheux@.umontreal.ca) writes:
> CREATE PROCEDURE comptabilisationDYN
> @.colonne varchar(3) '*receives R1, then R2, loop is in vba access*
> AS
> DECLARE @.SQLStatement varchar(8000)
> DECLARE @.TotalNum4 int
> DECLARE @.TotalNum3 int
> DECLARE @.TotalNum2 int
> DECLARE @.TotalNum1 int
> SELECT SQLStatement = 'SELECT COUNT(*) FROM
> dbo.Tbl_Rponses_tudiants WHERE' + @.colonne + '=4 AND YY = @.year'
> EXEC sp_executesql @.SQLStatement, N'@.TotalNum4 int OUTPUT', @.TotalNum4
> OUTPUT

You need:

SELECT SQLStatement = 'SELECT @.TotalNum4 = COUNT(*) FROM
dbo.Tbl_Rponses_tudiants WHERE' + @.colonne + '=4 AND YY = @.year'
EXEC sp_executesql @.SQLStatement, N'@.TotalNum4 int OUTPUT', @.TotalNum4
OUTPUT

You also need to add @.year to the parameter list.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank You, but i have tried your code before and again and it still
doesn't work. I call the procedure and it seems to work but it doesn't
write anything in the table. It doesn't recognize the ouput variable in
the INSERT line.

Erland Sommarskog wrote:
> Patrik (patrik.maheux@.umontreal.ca) writes:
> > CREATE PROCEDURE comptabilisationDYN
> > @.colonne varchar(3) '*receives R1, then R2, loop is in vba access*
> > AS
> > DECLARE @.SQLStatement varchar(8000)
> > DECLARE @.TotalNum4 int
> > DECLARE @.TotalNum3 int
> > DECLARE @.TotalNum2 int
> > DECLARE @.TotalNum1 int
> > SELECT SQLStatement = 'SELECT COUNT(*) FROM
> > dbo.Tbl_Rponses_tudiants WHERE' + @.colonne + '=4 AND YY =
@.year'
> > EXEC sp_executesql @.SQLStatement, N'@.TotalNum4 int OUTPUT',
@.TotalNum4
> > OUTPUT
> You need:
> SELECT SQLStatement = 'SELECT @.TotalNum4 = COUNT(*) FROM
> dbo.Tbl_Rponses_tudiants WHERE' + @.colonne + '=4 AND YY =
@.year'
> EXEC sp_executesql @.SQLStatement, N'@.TotalNum4 int OUTPUT',
@.TotalNum4
> OUTPUT
> You also need to add @.year to the parameter list.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

Your personal narratives and pseudo-code are useless.|||Patrik (patrik.maheux@.umontreal.ca) writes:
> Thank You, but i have tried your code before and again and it still
> doesn't work. I call the procedure and it seems to work but it doesn't
> write anything in the table. It doesn't recognize the ouput variable in
> the INSERT line.

Could you post the exact code you have now. Looking back on your post,
I see now that there will be a syntax error from the dynamic SQL.

Judging from the code you posted, you should always get a row insered,
even if only a NULL value.

I assume that "seems to work" does not mean that you don't get any
error messages. But maybe you should try running the procedure from
Query Analyzer, in case you have poor error handling in your Access
code.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Patrik (patrik.maheux@.umontreal.ca) writes:
> > Thank You, but i have tried your code before and again and it still
> > doesn't work. I call the procedure and it seems to work but it
doesn't
> > write anything in the table. It doesn't recognize the ouput
variable in
> > the INSERT line.
> Could you post the exact code you have now. Looking back on your
post,
> I see now that there will be a syntax error from the dynamic SQL.
> Judging from the code you posted, you should always get a row
insered,
> even if only a NULL value.
> I assume that "seems to work" does not mean that you don't get any
> error messages. But maybe you should try running the procedure from
> Query Analyzer, in case you have poor error handling in your Access
> code.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Thank You, after reading Mr.Sommarskog article more profoundly again
over the week-end, I pick-up my mistake. I wasn't using the parameter
list, now it works fine. Here's my final code.

CREATE PROCEDURE ComptabilisationDYN
@.numsaisie int,
@.code int,
@.colonne varchar(3)
AS

DECLARE @.TotalNum4 int
DECLARE @.SQL1 nvarchar(1000)
DECLARE @.paramlist nvarchar(1000)

SELECT @.SQL1 ='SELECT @.TotalNum4 = COUNT(*) FROM
dbo.Tbl_Rponses_tudiants WHERE ' +@.colonne + '=''4'''

SELECT @.paramlist = '@.TotalNum4 int OUTPUT'
EXEC sp_executesql @.SQL1, @.paramlist, @.TotalNum4 OUTPUT

INSERT INTO comptabilisation(Num_Saisie, code, Total4) VALUES
(@.numsaisie,@.code, @.TotalNum4)
GO|||Erland Sommarskog wrote:
> Patrik (patrik.maheux@.umontreal.ca) writes:
> > Thank You, but i have tried your code before and again and it still
> > doesn't work. I call the procedure and it seems to work but it
doesn't
> > write anything in the table. It doesn't recognize the ouput
variable in
> > the INSERT line.
> Could you post the exact code you have now. Looking back on your
post,
> I see now that there will be a syntax error from the dynamic SQL.
> Judging from the code you posted, you should always get a row
insered,
> even if only a NULL value.
> I assume that "seems to work" does not mean that you don't get any
> error messages. But maybe you should try running the procedure from
> Query Analyzer, in case you have poor error handling in your Access
> code.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Dynamic SQL to populate a variable

Here's the WRONG way to do what I want. I need a way to populate a variable from the output of a dynamic query.

declare @.TableName sysname

set @.TableName = 'Customers'

delcare @.Output bigint

declare @.SQL varchar(max)

set @.SQL = 'select top 1 RowID from ' + @.TableName

select @.Output =

EXEC (@.SQL)

create function udf_GetDatabaseFingerPrint(

@.DBID bigint

)

begin

returns bigint

as

declare @.dbname sysname

, @.iBig bigint

, @.tSQL varchar(2000)

select @.dbName = Name from Master.Dbo.Sysdatabases where DBID = @.DBID

set @.tSQL = 'select sum(Rows) from ' + @.dbName + '.dbo.sysindexes'

set @.iBig = exec(@.tSQL)

return @.iBig

end

Number one, you cannot do this in a T-SQL function. Functions will not allow such things. You can use sp_executeSQL. In this case, something like this (and old example I had

declare @.objectId int,

exec sp_executeSQL

N'select @.objectId = max(object_id) from sys.objects',

N'@.objectId int output', @.objectId=@.objectId output

select @.objectId