Passing Paramenter to SP for Column Name

I need to select status values form 1 of 4 possible columns, and I need to
pass the column name to select on as a parameter to the stored procedure.
Does anyone have an example of the syntax for the stored procedure?

such as:

CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
(
@.SelectColumn nVarChar
)
AS

SET NOCOUNT ON;

SELECT *
FROM Documents
WHERE (@.SelectColumn = 0)

The columns to select on are BIT columns.

The error message on the above SP is:

'Syntax error converting the nvarchar value 'P' to a column of data type
int.'

At this point, the passed in parameter is a string "ProducerStatus"

Thanks
Michaelhi Michael,
You need to use the Dynamic Sql to change the column name at
the run time.

create procedure dbo.sp_Document_select_bystatus
(@.selectColumn varchar(255))
as
set nocount on
declare @.dynamicSql varchar(8000)

select @.dynamicSql = '
SELECT *
FROM Documents
WHERE ( ' + @.selectColumn + ' = 0)
'
execute (@.dynamicSql)
set nocount off
Go

Thank you
santhosh
Michael Jackson wrote:
> I need to select status values form 1 of 4 possible columns, and I
need to
> pass the column name to select on as a parameter to the stored
procedure.
> Does anyone have an example of the syntax for the stored procedure?
> such as:
> CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
> (
> @.SelectColumn nVarChar
> )
> AS
> SET NOCOUNT ON;
> SELECT *
> FROM Documents
> WHERE (@.SelectColumn = 0)
> The columns to select on are BIT columns.
> The error message on the above SP is:
> 'Syntax error converting the nvarchar value 'P' to a column of data
type
> int.'
> At this point, the passed in parameter is a string "ProducerStatus"
> Thanks
> Michael|||Thanks for the help. It worked great.

"SSK" <suthramsk@.yahoo.com> wrote in message
news:1107491299.712183.231340@.z14g2000cwz.googlegr oups.com...
> hi Michael,
> You need to use the Dynamic Sql to change the column name at
> the run time.
> create procedure dbo.sp_Document_select_bystatus
> (@.selectColumn varchar(255))
> as
> set nocount on
> declare @.dynamicSql varchar(8000)
> select @.dynamicSql = '
> SELECT *
> FROM Documents
> WHERE ( ' + @.selectColumn + ' = 0)
> '
> execute (@.dynamicSql)
> set nocount off
> Go
> Thank you
> santhosh
> Michael Jackson wrote:
>> I need to select status values form 1 of 4 possible columns, and I
> need to
>> pass the column name to select on as a parameter to the stored
> procedure.
>> Does anyone have an example of the syntax for the stored procedure?
>>
>> such as:
>>
>> CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
>> (
>> @.SelectColumn nVarChar
>> )
>> AS
>>
>> SET NOCOUNT ON;
>>
>> SELECT *
>> FROM Documents
>> WHERE (@.SelectColumn = 0)
>>
>> The columns to select on are BIT columns.
>>
>> The error message on the above SP is:
>>
>> 'Syntax error converting the nvarchar value 'P' to a column of data
> type
>> int.'
>>
>> At this point, the passed in parameter is a string "ProducerStatus"
>>
>> Thanks
>> Michael|||Avoid dynamic SQL if you can. In this case you don't need it:

SELECT col1
FROM Documents
WHERE col1 = 0 AND @.selectcolumn = 'col1'
UNION ALL
SELECT col2
FROM Documents
WHERE col2 = 0 AND @.selectcolumn = 'col2'
UNION ALL
SELECT col3
FROM Documents
WHERE col3 = 0 AND @.selectcolumn = 'col3'
UNION ALL
SELECT col4
FROM Documents
WHERE col4 = 0 AND @.selectcolumn = 'col4'

To understand why dynamic SQL isn't a good idea for this, see:

http://www.sommarskog.se/dynamic_sql.html

--
David Portas
SQL Server MVP
--|||Michael Jackson (stratojack@.cox.net) writes:
> I need to select status values form 1 of 4 possible columns, and I need to
> pass the column name to select on as a parameter to the stored procedure.
> Does anyone have an example of the syntax for the stored procedure?
> such as:
> CREATE PROCEDURE dbo.sp_Document_Select_ByStatus
> (
> @.SelectColumn nVarChar
> )
> AS

To add to the other responses, permit me to point out two other flaws:

1) sp_ is a prefix that is reserved for system procedures, and SQL Server
will first look for these in master. Don't use it for your own code.

2) nvarchar without lengthspeciication is the same as nvarchar(1), hardly
what you want.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

passing information

What i am trying to do is have the contact information to be displayed also. The contact information is in dbo.CONTSUPP under column 'contact'. Is there anyway to pass that 'contact' value up to the parent select statement. So the result will show Company, Address1,...,Source, contact.

SELECT Company, Address1, Address2,
Address3, City, State, Zip,
Country, Phone1, Fax, Source
FROM dbo.CONTACT1
WHERE dbo.CONTACT1.ACCOUNTNO IN (
SELECT ACCOUNTNO
FROM dbo.CONTSUPP
WHERE contact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
GROUP BY ACCOUNTNO
HAVING COUNT(*) <= 1
)yup, derived table:

SELECT Company, Address1, Address2,
Address3, City, State, Zip,
Country, Phone1, Fax, Source
,t1.CONTSUPP
FROM dbo.CONTACT1 INNER JOIN
(
SELECT ACCOUNTNO, CONTSUPP
FROM dbo.CONTSUPP
WHERE contact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
GROUP BY ACCOUNTNO
HAVING COUNT(*) <= 1
) As t1 ON t1.ACCOUNTNO = CONTACT1.ACCOUNTNO

You get the benefit of only generating the derived table once as well, as opposed to being evaluated once for each record when placed in the WHERE clause.|||Thank you, you pointed me in the right direction. There was one issue with the code you wrote because you cant group by ACCOUNTNO because the select has ACCOUNTNO and CONTACT. Anyway this is what the currently working code looks like. Thank you again, without your help I would not have been able to do this.

SELECT Company, Address1, Address2,
Address3, City, State, Zip,
Country, Phone1, Fax, Source,
t1.contact AS 'Device'
FROM dbo.CONTACT1
INNER JOIN (
SELECT accountno, contact
FROM dbo.CONTSUPP
WHERE accountno IN (
SELECT accountno
FROM dbo.CONTSUPP
WHERE contact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
GROUP BY accountno
HAVING COUNT(*) <= 1
) AND (
contact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
)
) AS t1
ON dbo.CONTACT1.accountno = t1.accountno

passing in a value to use as a column 'as name' in a stored proc

Hi,
I want to hand into a store procude the column name to use in the returned
result set...
create proc sample
@.colName as nvarcher(20)
as
select col1 as @.colname, col2 from table1..
But this produces an error... saying incorrect syntax near @.colname
is there a way to do want i am trying to do here?
ThanksThe curse and blessings of dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Martin C K Poon
Senior Analyst Programmer
====================================
"Aussie Rules" <AussieRules@.nospam.nospam> bl
news:uvQ1pgOjGHA.3572@.TK2MSFTNGP04.phx.gbl g...
> Hi,
> I want to hand into a store procude the column name to use in the returned
> result set...
> create proc sample
> @.colName as nvarcher(20)
> as
> select col1 as @.colname, col2 from table1..
> But this produces an error... saying incorrect syntax near @.colname
> is there a way to do want i am trying to do here?
> Thanks
>
>
>|||Thanks for Martin's informative inputs.
Hi Aussie,
I agree with Martin that you would need to consider using the dynamic SQL
execution. And in SQL Server the "exec" or "execute" keyword to execute
dynamic generated T-SQL statements:
#EXECUTE
http://msdn.microsoft.com/library/e...asp?frame=true
BTW, dynamic sql will have additional performance overhead comparing to
static T-SQL execution. Also, when we use string concatenate to generate
dynamic dynamic T-SQL statement, we would also take care of SQL injection
issue:
#SQL Injection
http://msdn2.microsoft.com/en-us/library/ms161953.aspx
Hope this also helps.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Aussie,
Have you got any progress or new ideas on this issue or does our replies
help you some? If there is still anything we can help, please feel free to
post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Passing Formula as a parameter to SQL Query in AddCommand

Hi,

I'am new to Crystal reports,

I created a formula based on that i have to fetch column value from database

for that i wrote a very simple query in SQLCOMMAND

as SELECT <TABLE.CPLUMN> FROM <TABLE>

WHERE <TABLE.OTHERCOLUMN>='@.CRYSTAL REPORT FORMULA'

when closing the commandbox i'am getting error "OLE DB ERROR NO DATABSE RECORDS FOUND"

if i pass a some hardcoded vale(data whic exists in database) then the query is not throwing any error..

Thanks
MikeI don't think you can do that. You can create and use parameters in you query but not formulas. Your query looks really simple, so I have some doubts if it was that necessary to create it in your case.

Why don't you use something like that:

if {TABLE.OTHERCOLUMN}>=here print what you have in your '@.CRYSTAL REPORT FORMULA' then {TABLE.OTHERCOLUMN}

This formula does the same.

Passing Field values in a URL

Hello,
I need to pass field values to a URL when we click on a particular datavalue
on the Report. Basically I am trying to put up a URL on a column of the
report. And I would like to pass that corresponding column value dynamically.
Please someone let me know how to do that. At present I was trying to do
something like this that doesnt work :
http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
where DATAKEY is one of my fields. But this doesnt help me pass the dynamic
values of that particular row. Please someone give me a idea.
Thanks,
BabithaAre you trying to pass parameters into the Rpt Services report to be used for
rendering, or are you trying to extract them out of a rendered report?
sebring1130
"Babitha" wrote:
> Hello,
> I need to pass field values to a URL when we click on a particular datavalue
> on the Report. Basically I am trying to put up a URL on a column of the
> report. And I would like to pass that corresponding column value dynamically.
> Please someone let me know how to do that. At present I was trying to do
> something like this that doesnt work :
> http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> where DATAKEY is one of my fields. But this doesnt help me pass the dynamic
> values of that particular row. Please someone give me a idea.
> Thanks,
> Babitha|||I have the same problem, I am tryinf to read a row from reportviewer control
(data is alrady shown by reportviewer on the screen and I need to select one
line and process it), is there ny example?
"sebring1130" wrote:
> Are you are trying to pass parameters into the Rpt Services report to be used for
> rendering, or are you trying to extract them out of a rendered report?
> sebring1130
>
> "Babitha" wrote:
> > Hello,
> >
> > I need to pass field values to a URL when we click on a particular datavalue
> > on the Report. Basically I am trying to put up a URL on a column of the
> > report. And I would like to pass that corresponding column value dynamically.
> >
> > Please someone let me know how to do that. At present I was trying to do
> > something like this that doesnt work :
> >
> > http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> >
> > where DATAKEY is one of my fields. But this doesnt help me pass the dynamic
> > values of that particular row. Please someone give me a idea.
> >
> > Thanks,
> > Babitha|||I too am trying to achive the same outcome. I don't think this is going to
be doable.
"Babitha" <Babitha@.discussions.microsoft.com> wrote in message
news:C3DC6A35-6BB7-4400-94D1-37AD5E8429EE@.microsoft.com...
> Hello,
> I need to pass field values to a URL when we click on a particular
datavalue
> on the Report. Basically I am trying to put up a URL on a column of the
> report. And I would like to pass that corresponding column value
dynamically.
> Please someone let me know how to do that. At present I was trying to do
> something like this that doesnt work :
> http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> where DATAKEY is one of my fields. But this doesnt help me pass the
dynamic
> values of that particular row. Please someone give me a idea.
> Thanks,
> Babitha|||Have you tried using the hyperlink feature of the cells on the report? If
you go into the texbox cell's properties and hit the "navigation" tab there
are several options to set up hyperlinks so that when you click on a cell on
the report you can automatically jump to a new URL. It looks like the URL
can be anything ... I'd be very surprized if you couldn't insert parameters
and field values in the the URL.
sebring1130
"Art Simcoe" wrote:
> I too am trying to achive the same outcome. I don't think this is going to
> be doable.
> "Babitha" <Babitha@.discussions.microsoft.com> wrote in message
> news:C3DC6A35-6BB7-4400-94D1-37AD5E8429EE@.microsoft.com...
> > Hello,
> >
> > I need to pass field values to a URL when we click on a particular
> datavalue
> > on the Report. Basically I am trying to put up a URL on a column of the
> > report. And I would like to pass that corresponding column value
> dynamically.
> >
> > Please someone let me know how to do that. At present I was trying to do
> > something like this that doesnt work :
> >
> > http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> >
> > where DATAKEY is one of my fields. But this doesnt help me pass the
> dynamic
> > values of that particular row. Please someone give me a idea.
> >
> > Thanks,
> > Babitha
>
>|||I am also trying to achieve this very ting. It appears you cannot insert
parameters and field values into the URL. You can build the expression but
the resulting URL simply contains the variable name you enter, not its value.
Anyone solved this?
"sebring1130" wrote:
> Have you tried using the hyperlink feature of the cells on the report? If
> you go into the texbox cell's properties and hit the "navigation" tab there
> are several options to set up hyperlinks so that when you click on a cell on
> the report you can automatically jump to a new URL. It looks like the URL
> can be anything ... I'd be very surprized if you couldn't insert parameters
> and field values in the the URL.
> sebring1130
>
> "Art Simcoe" wrote:
> > I too am trying to achive the same outcome. I don't think this is going to
> > be doable.
> >
> > "Babitha" <Babitha@.discussions.microsoft.com> wrote in message
> > news:C3DC6A35-6BB7-4400-94D1-37AD5E8429EE@.microsoft.com...
> > > Hello,
> > >
> > > I need to pass field values to a URL when we click on a particular
> > datavalue
> > > on the Report. Basically I am trying to put up a URL on a column of the
> > > report. And I would like to pass that corresponding column value
> > dynamically.
> > >
> > > Please someone let me know how to do that. At present I was trying to do
> > > something like this that doesnt work :
> > >
> > > http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> > >
> > > where DATAKEY is one of my fields. But this doesnt help me pass the
> > dynamic
> > > values of that particular row. Please someone give me a idea.
> > >
> > > Thanks,
> > > Babitha
> >
> >
> >|||What you need to to is to create the string in the expressions:
i.e.
="http://blah.mmm.com/etc etc" & fields!fieldname.value
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stuart" <Stuart@.discussions.microsoft.com> wrote in message
news:EB4306DC-595A-447F-A07D-B5A7BE18C1EF@.microsoft.com...
> I am also trying to achieve this very ting. It appears you cannot insert
> parameters and field values into the URL. You can build the expression
but
> the resulting URL simply contains the variable name you enter, not its
value.
> Anyone solved this?
> "sebring1130" wrote:
> > Have you tried using the hyperlink feature of the cells on the report?
If
> > you go into the texbox cell's properties and hit the "navigation" tab
there
> > are several options to set up hyperlinks so that when you click on a
cell on
> > the report you can automatically jump to a new URL. It looks like the
URL
> > can be anything ... I'd be very surprized if you couldn't insert
parameters
> > and field values in the the URL.
> >
> > sebring1130
> >
> >
> > "Art Simcoe" wrote:
> >
> > > I too am trying to achive the same outcome. I don't think this is
going to
> > > be doable.
> > >
> > > "Babitha" <Babitha@.discussions.microsoft.com> wrote in message
> > > news:C3DC6A35-6BB7-4400-94D1-37AD5E8429EE@.microsoft.com...
> > > > Hello,
> > > >
> > > > I need to pass field values to a URL when we click on a particular
> > > datavalue
> > > > on the Report. Basically I am trying to put up a URL on a column of
the
> > > > report. And I would like to pass that corresponding column value
> > > dynamically.
> > > >
> > > > Please someone let me know how to do that. At present I was trying
to do
> > > > something like this that doesnt work :
> > > >
> > > > http://serververname/WebForm2.aspx?param1=Fields!DATAKEY.value
> > > >
> > > > where DATAKEY is one of my fields. But this doesnt help me pass the
> > > dynamic
> > > > values of that particular row. Please someone give me a idea.
> > > >
> > > > Thanks,
> > > > Babitha
> > >
> > >
> > >

Passing documents > 8000 chars to sp_xml_preparedocument

We are storing incoming xmldocumkents in a TEXT column of a table.
I've been trying to find a way of lifting that text data out of the table and passing the data to sp_xml_preparedocument for further processing with OPENXML.
This is fine if the data is < 8000 bytes, however our documents are going to be larger than that. Given that we cannot create a local variable of type TEXT, how do we work around this?
We made one attempt to chunk the data into a temporary table and rebuild it into an sp TEXT parameter as below, but it didn't work.
Any ideas greatly appreciated
Thanks
Mark
CREATE TABLE [dbo].[tblMessage] (
[tblMessageId] [int] IDENTITY (1, 1) NOT NULL ,
[tblmessageXML] [text] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
--This is still < 8000
INSERT INTO tblMessage (tblmessageXML) VALUES ('<NewDataSet><Table><tblPrimaryQueueID>5</tblPrimaryQueueID><tblPeopleStagingID>9</tblPeopleStagingID><tblPeopleStagingStatus>0</tblPeopleStagingStatus><tblPeopleStagingPeopleID>1 6</tblPeopleStagingPeopleID><
tblPeopleStagingPeopleFName>Homer</tblPeopleStagingPeopleFName><tblPeopleStagingPeopl eLName>Simpson</tblPeopleStagingPeopleLName><tblPeopleStagingPeopl eCauswayURN>16201</tblPeopleStagingPeopleCauswayURN></Table></NewDataSet>')
create procedure xmltest2(@.XMLTEXT TEXT)
AS
declare @.dl as int
declare @.Ct as int
declare @.cs as Int
--declare @.XMLtext as VARCHAR(8000)
declare @.tmpXML as VARCHAR(8000)
declare @.MessID as INT
SET @.MessID = 1
set @.cs = 20
select @.dl = DataLength(tblMessageXML) from tblMessage
drop table #t
create table #t (tid INT IDENTITY, tmid INT, tvc VARCHAR(20))
SET @.ct = 1
WHILE @.CT < @.DL
BEGIN
INSERT INTO #t (tmid, tvc)
SELECT @.MessID, SUBSTRING(tblMessageXML, @.CT, @.cs)
FROM tblMessage
--WHERE tblMessageID = @.MessID
SET @.CT = @.CT + @.CS
END
DECLARE XML_CURSOR CURSOR FAST_FORWARD
FOR
SELECT tvc
FROM #t
ORDER BY TID
OPEN XML_CURSOR
FETCH NEXT FROM XML_CURSOR INTO @.TMPXML
--SET @.XMLTEXT = ''
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.XMLTEXT = @.XMLTEXT + @.TMPXML
FETCH NEXT FROM XML_CURSOR INTO @.TMPXML
END
CLOSE XML_CURSOR
DEALLOCATE XML_CURSOR
Hi
Check out:
http://sqlxml.org/faqs.aspx?faq=42
John
"Mark McCormick" <anonymous@.discussions.microsoft.com> wrote in message
news:0C8F6CD3-A116-40C4-9468-9B31F975B32F@.microsoft.com...
> We are storing incoming xmldocumkents in a TEXT column of a table.
> I've been trying to find a way of lifting that text data out of the table
and passing the data to sp_xml_preparedocument for further processing with
OPENXML.
> This is fine if the data is < 8000 bytes, however our documents are going
to be larger than that. Given that we cannot create a local variable of
type TEXT, how do we work around this?
> We made one attempt to chunk the data into a temporary table and rebuild
it into an sp TEXT parameter as below, but it didn't work.
> Any ideas greatly appreciated
> Thanks
> Mark
>
> CREATE TABLE [dbo].[tblMessage] (
> [tblMessageId] [int] IDENTITY (1, 1) NOT NULL ,
> [tblmessageXML] [text] COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> --This is still < 8000
> INSERT INTO tblMessage (tblmessageXML) VALUES
('<NewDataSet><Table><tblPrimaryQueueID>5</tblPrimaryQueueID><tblPeopleStagi
ngID>9</tblPeopleStagingID><tblPeopleStagingStatus>0</tblPeopleStagingStatus
><tblPeopleStagingPeopleID>16</tblPeopleStagingPeopleID><tblPeopleStagingPeo
pleFName>Homer</tblPeopleStagingPeopleFName><tblPeopleStagingPeopl eLName>Sim
pson</tblPeopleStagingPeopleLName><tblPeopleStagingPeopl eCauswayURN>16201</t
blPeopleStagingPeopleCauswayURN></Table></NewDataSet>')
> create procedure xmltest2(@.XMLTEXT TEXT)
> AS
> declare @.dl as int
> declare @.Ct as int
> declare @.cs as Int
> --declare @.XMLtext as VARCHAR(8000)
> declare @.tmpXML as VARCHAR(8000)
> declare @.MessID as INT
> SET @.MessID = 1
> set @.cs = 20
> select @.dl = DataLength(tblMessageXML) from tblMessage
> drop table #t
> create table #t (tid INT IDENTITY, tmid INT, tvc VARCHAR(20))
> SET @.ct = 1
> WHILE @.CT < @.DL
> BEGIN
> INSERT INTO #t (tmid, tvc)
> SELECT @.MessID, SUBSTRING(tblMessageXML, @.CT, @.cs)
> FROM tblMessage
> -- WHERE tblMessageID = @.MessID
> SET @.CT = @.CT + @.CS
> END
> DECLARE XML_CURSOR CURSOR FAST_FORWARD
> FOR
> SELECT tvc
> FROM #t
> ORDER BY TID
>
> OPEN XML_CURSOR
> FETCH NEXT FROM XML_CURSOR INTO @.TMPXML
> --SET @.XMLTEXT = ''
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.XMLTEXT = @.XMLTEXT + @.TMPXML
> FETCH NEXT FROM XML_CURSOR INTO @.TMPXML
> END
> CLOSE XML_CURSOR
> DEALLOCATE XML_CURSOR
>
>
|||Another good one to have a look at
http://www.experts-exchange.com/Data..._20670044.html

Passing Date Variables

I am a newbie and need help. I am creating an intranet site for my company
and my SQL2000 database has a column named transdate. I want to provide the
user with the ability to run a canned report depending on a date range
entered in a web form (Start Date / End Date).
How would I pass these form entries to my SQL select statement? I am using
ASP VBScript
I'm thinking...
Select *
from DB where transdate >= ' and transdate <= '
Your help is greatly appreciated!!!
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1> How would I pass these form entries to my SQL select statement? I am using
> ASP VBScript
You can use a parameterized SQL statement, specifying '?' as parameter
markers:
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = myConnection
command.CommandText = "SELECT * FROM MyTable WHERE transdate >= ? AND
transdate <= ?"
Set fromDateParameter = command.CreateParameter( _
"@.fromDate", adDate, adParamInput)
command.Parameters.Append fromDateParameter
fromDateParameter.Value = "2006-05-01"
Set toDateParameter = command.CreateParameter( _
"@.toDateParameter", adDate, adParamInput)
command.Parameters.Append toDateParameter
toDateParameter.Value = "2006-05-31"
Hope this helps.
Dan Guzman
SQL Server MVP
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:615867ef9c88a@.uwe...
>I am a newbie and need help. I am creating an intranet site for my company
> and my SQL2000 database has a column named transdate. I want to provide
> the
> user with the ability to run a canned report depending on a date range
> entered in a web form (Start Date / End Date).
> How would I pass these form entries to my SQL select statement? I am using
> ASP VBScript
> I'm thinking...
> Select *
> from DB where transdate >= ' and transdate <= '
> Your help is greatly appreciated!!!
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Thanks Dan, I'll give it a shot
Dan Guzman wrote:
>You can use a parameterized SQL statement, specifying '?' as parameter
>markers:
>Set command = CreateObject("ADODB.Command")
>command.ActiveConnection = myConnection
>command.CommandText = "SELECT * FROM MyTable WHERE transdate >= ? AND
>transdate <= ?"
>Set fromDateParameter = command.CreateParameter( _
> "@.fromDate", adDate, adParamInput)
>command.Parameters.Append fromDateParameter
>fromDateParameter.Value = "2006-05-01"
>Set toDateParameter = command.CreateParameter( _
> "@.toDateParameter", adDate, adParamInput)
>command.Parameters.Append toDateParameter
>toDateParameter.Value = "2006-05-31"
>
>[quoted text clipped - 11 lines]
Message posted via http://www.webservertalk.com|||Obviously I am not advanced enough to get this? I need to pass the dates
from my Web form to the embedded SQL statement in Dreamweaver. I am using
multiple recordsets that require these same date ranges. In ACCESS it is eas
y
because you can create the form and reference it. Is there anything like thi
s
in SQL?
Dan Guzman wrote:
>You can use a parameterized SQL statement, specifying '?' as parameter
>markers:
>Set command = CreateObject("ADODB.Command")
>command.ActiveConnection = myConnection
>command.CommandText = "SELECT * FROM MyTable WHERE transdate >= ? AND
>transdate <= ?"
>Set fromDateParameter = command.CreateParameter( _
> "@.fromDate", adDate, adParamInput)
>command.Parameters.Append fromDateParameter
>fromDateParameter.Value = "2006-05-01"
>Set toDateParameter = command.CreateParameter( _
> "@.toDateParameter", adDate, adParamInput)
>command.Parameters.Append toDateParameter
>toDateParameter.Value = "2006-05-31"
>
>[quoted text clipped - 11 lines]
Message posted via http://www.webservertalk.com|||> Obviously I am not advanced enough to get this? I need to pass the dates
> from my Web form to the embedded SQL statement in Dreamweaver. I am using
> multiple recordsets that require these same date ranges. In ACCESS it is
> easy
> because you can create the form and reference it. Is there anything like
> this
> in SQL?
SQL Server is basically just the back-end database engine. Unlike SQL
Server, Access also includes an IDE so that you can develop a 'rich client'
GUI for your users. The Access database engine isn't a client/server DBMS
because Access runs in the client process and, in the case of a multi-user
application, the Access database file is shared among multiple Access
instances. With SQL Server, it is the database engine is shared and only
that SQL Server instance accesses the database files.
I know next to nothing about Dreamweaver so I can't provide detailed help.
I don't know what an 'embedded SQL statement in Dreamweaver' is. I assume
this part of server-side code (ASP or ASP.NET) that is generated by the
Dreamweaver IDE. I would expect that the IDE would provide some method to
parameterize the SQL statement, map to your form variables and associate
with a SQL Server database connection.
don't know if this will help but below is an ASP VBScript snippet that can
execute a SQL statement based on the date range. I would expect Dreamweaver
would generate something similar.
<!-- include ADO constants -->
<!-- METADATA
TYPE="typelib"
UUID="00000200-0000-0010-8000-00AA006D2EA4"
-->
<%
Set connection = CreateObject("ADODB.Connection")
connection,Open "Provider=SQLOLEDB;Data Source=MyDbServer;Integrated
Security=SSPI"
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = myConnection
command.CommandText = "SELECT * FROM MyTable " & _
"WHERE transdate >= ? AND transdate <= ?"
Set fromDateParameter = command.CreateParameter( _
"@.fromDate", adDate, adParamInput)
command.Parameters.Append fromDateParameter
fromDateParameter.Value = Request("fromDate")
Set toDateParameter = command.CreateParameter( _
"@.toDateParameter", adDate, adParamInput)
command.Parameters.Append toDateParameter
toDateParameter.Value = Request("toDate")
Set results = command.Execute
While results.EOF = False
'process row here
results.MoveNext
Loop
results.Close
connection,Close
&>
Hope this helps.
Dan Guzman
SQL Server MVP
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:61bd9dcc71568@.uwe...
> Obviously I am not advanced enough to get this? I need to pass the dates
> from my Web form to the embedded SQL statement in Dreamweaver. I am using
> multiple recordsets that require these same date ranges. In ACCESS it is
> easy
> because you can create the form and reference it. Is there anything like
> this
> in SQL?
> Dan Guzman wrote:
> --
> Message posted via http://www.webservertalk.comsql

Passing Column Name as parameter to sql store procedure

i am using asp.net 2005 with sql server 2005. in my database table contains

Table Name : Page_Content

Page_Id

1011021AbcPqr2Lmnoiu

ALTER PROCEDURE[dbo].[SELECT_CONTENT]

(@.lang_codevarchar(max))

AS

begin

declare@.aas varchar(max)set@.a = @.lang_code

Selectpage_id,@.aFrompage_content

end

Here in this above store procedure i want to pass 101 to @.lang_code

here is my output, but this is wrong output

Page_Id

Column111012101

but i want following output

Page_Id

1011021AbcPqr2Lmnoiu

use dynamic sql.http://www.sommarskog.se/dyn-search.html
modify your procedure as:-

declare @.sql
set @.sql = 'Selectpage_id,' + @.a + 'Frompage_content'
exec sp_executesql @.sql

hope it helps

|||

aadreja:

use dynamic sql.http://www.sommarskog.se/dyn-search.html
modify your procedure as:-

declare @.sql
set @.sql = 'Selectpage_id,' + @.a + 'Frompage_content'
exec sp_executesql @.sql

hope it helps

The above code is subject to sql injection attacks. Query on sql injection attacks if you don't know what they are.

As coded, someone could force your page to reveal sensitive data in other tables, or alter or destroy data in your database in ways you do not want to allow.

Given that a column name has very specific naming rules, you can test that the value you get in @.a is a plausible, safe column name.

If @.a has any character other than a letter from a-z, A-Z or 0-9, you should trap that and raise an error.

One way to test is to make a copy of @.a and remove all the valid characters. If nothing is left in the copy, it's a safe column name to process.

|||

I know you could do this with dynamic sql, but that's not always the best solution.

I think you could use a CASE/WHEN block to do what you are wanting. Each field would have to be known in advance, this wouldn't work "on the fly" if you add new columns to the table without updating the SP

ALTER PROCEDURE[dbo].[SELECT_CONTENT]

(@.lang_codevarchar(max))

AS

begin

declare@.aas varchar(max)set@.a = @.lang_code

Selectpage_id,
CASE
WHEN @.a = '101' THEN 101
WHEN @.a = '102' THEN 102
ELSE 101 -- you don't need an else, but this query will fail in a syntax error if the input doesn't match one of your defined values.
END Frompage_content

end

|||

I agree!

Benefits of your approach:

Sql Injection safe

passing column name as parameter to a stored procedure

Hi!
I want to pass a column name, sometimes a table name, to a stored
procedure, but it didn't work. I tried to define the data type as
char, vachar, nchar, text, but the result were same. Any one know how
to get it work?
Thanks a lot!!
Saiyou[posted and mailed, please reply in news]

Saiyou Anh (wangc@.alexian.net) writes:
> I want to pass a column name, sometimes a table name, to a stored
> procedure, but it didn't work. I tried to define the data type as
> char, vachar, nchar, text, but the result were same. Any one know how
> to get it work?

So why do you need to do this?

While this is possible to do this, you might essentially be throwing
out the baby with the bathtub and loose most of the advantages of
stored procedures. Often this is a token of bad design.

Anyway, I have a longer article on my web site that shows you how to do
it - and why you probably shouldn't.

http://www.sommarskog.se/dynamic_sql.html

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Passing Column Name as Parameter (SPROC)

Hello, I don't know a lot of T-SQL. I have a table with 49 columns and 48 rows (US States). The fist column is a list of "from" states and the other columns are named for each state. The point is to look up a variable column for a variable row to obtain a state to state transit rate. I tried everything I could find and no luck. If someone can help I would appreciate it, and if not thanks anyway. Gregg

You could use dynamic SQL from this problem:

Code Snippet

create table rates

(

from_state char(2),

WA decimal,

NY decimal,

CA decimal

)

insert into rates values('WA',0,1,10)

insert into rates values('NY',2,0,20)

insert into rates values('CA',3,30,0)

create procedure GetRate

@.from_state char(2),

@.to_state char(2)

AS

BEGIN

declare @.query varchar(100)

set @.query = 'select '+@.to_state+' from rates where from_state='''+@.from_state+''''

execute ( @.query)

END

But this method has SQL Injections.

Partly you could solve this problem by following code:

Code Snippet

create procedure GetRate2

@.from_state char(2),

@.to_state char(2)

AS

BEGIN

declare @.query nvarchar(100)

set @.query = 'select '+@.to_state+' from rates where from_state=@.from_state'

EXEC sp_executesql @.query, N'@.from_state char(2)', @.from_state=@.from_state

END

But best solution is convert you table for following format:

Code Snippet

create table rates2

(

from_state char(2),

to_state char(2),

rate decimal

)

|||

Here the sample ..

Code Snippet

--Sample table (first 10 States)

Create Table #statesfare (

[Starting From] Varchar(100) ,

[Alabama] Varchar(100) ,

[Alaska] Varchar(100) ,

[Arizona] Varchar(100) ,

[Arkansas] Varchar(100) ,

[California] Varchar(100) ,

[Colorado] Varchar(100) ,

[Connecticut] Varchar(100) ,

[Delaware] Varchar(100) ,

[Florida] Varchar(100) ,

[Georgia] Varchar(100)

);

--Random Generated data (Not For Scale, But City From & To same then ZERO)

Insert Into #statesfare Values('Alabama','0','21','29','90','82','65','72','84','4','51');

Insert Into #statesfare Values('Alaska','33','0','17','80','37','92','90','21','12','20');

Insert Into #statesfare Values('Arizona','62','19','0','97','23','66','22','43','94','60');

Insert Into #statesfare Values('Arkansas','61','38','12','0','98','42','68','70','81','87');

Insert Into #statesfare Values('California','92','27','82','72','0','43','84','39','24','80');

Insert Into #statesfare Values('Colorado','72','34','97','52','52','0','10','38','64','40');

Insert Into #statesfare Values('Connecticut','100','78','27','18','74','3','0','67','26','48');

Insert Into #statesfare Values('Delaware','93','49','20','88','45','3','60','0','57','77');

Insert Into #statesfare Values('Florida','79','80','37','12','90','30','24','48','0','50');

Insert Into #statesfare Values('Georgia','33','46','16','30','46','72','42','85','18','0');

Declare @.StartFrom as varchar(100);

Declare @.EndAt as varchar(100);

--Sample Input

Set @.StartFrom = 'Alabama'

Set @.EndAt = 'Georgia'

--Using Dynamic SQL (Supports Both SQL Server 2000 & 2005)

Exec ('Select ' + @.EndAt + ' Fare From #statesfare Where [Starting From] =''' +@.StartFrom + '''');

--Using UNPIVOT operator Only on SQL Server 2005

Select

[Starting From]

,[Target States]

,Fares

From

#statesfare P

UNPIVOT

(

Fares FOR [Target States] IN

(

[Alabama],

[Alaska],

[Arizona],

[Arkansas],

[California],

[Colorado],

[Connecticut],

[Delaware],

[Florida],

[Georgia]

)

) as UPVT

Where

[Starting From] = @.StartFrom

And [Target States] = @.EndAt

|||

I recommend to use quotename() function to avoid sql injection.

Code Snippet

set @.qeury = 'select ' + quotename(@.to_sate) + ' from ....';

quotename('abc') returns '[abc]';

Regards,

|||This is my table

FROM AL AR AZ CA AL 1.75 1.95 1.10 1.75 AR 1.50 1.50 1.50 1.75 AZ 1.50 1.50 1.50 1.75 CA 1.50 1.50 1.50 1.75

I Need to use a stored procedure to select one of these decimal rates based on two input parameters (@.fromstate AND @.tostate) and return the rate as an out put parameter(@.rate). I tried this even though I knew it was too simple to worrk.

ALTER PROCEDURE RC_Get_Rate

(

@.origstate nvarchar(255),

@.deststate nvarchar(255),

@.rate nvarchar(255) OUTPUT

)

AS

SET NOCOUNT ON

BEGIN

SELECT @.rate = @.deststate

FROM Rates48

WHERE Origin_State = @.origstate

END

RETURN

I'm sorry if you're previous posts already answered this, I thought I might not have been clear enougn as to my situation and my goal. I really did search a lot on this topic but got nowhere. Again if you can help (or already did) thanks, and if you can't thanks anyway, Gregg|||

here you go...

Code Snippet

Create Table rates48 (

[Origin_State] Varchar(100) ,

[AL] float ,

[AR] float ,

[AZ] float ,

[CA] float

);

Go

Insert Into rates48 Values('AL','1.75','1.95','1.10','1.75');

Insert Into rates48 Values('AR','1.50','1.50','1.50','1.75');

Insert Into rates48 Values('AZ','1.50','1.50','1.50','1.75');

Insert Into rates48 Values('CA','1.50','1.50','1.50','1.75');

Go

Create PROCEDURE RC_Get_Rate

(

@.origstate nvarchar(255),

@.deststate nvarchar(255),

@.rate nvarchar(255) OUTPUT

)

AS

SET NOCOUNT ON

BEGIN

Declare @.Query as NVarchar(1000);

Set @.Query = N'SELECT @.rateout = ' + quotename(@.deststate) + '

FROM Rates48

WHERE Origin_State = @.origstatein';

Exec sp_executesql @.Query, N'@.origstatein varchar(255), @.rateout nvarchar(255) output',@.origstatein = @.origstate, @.rateout=@.rate OUTPUT;

Return;

END

Go

Declare @.rate nvarchar(255)

Exec RC_Get_Rate 'AL', 'AZ', @.rate OUTPUT

Select @.rate

|||Thank You very much Manivannan.D.Sekaran. That works very well. I must admit though I don't know how. Could you maybe recommend a good book I could pick up to further myself on T-SQL. I would like to learn SPROC's, Functions, and some more advanced SQL. Well, again, thank you very much Manivannan.D.Sekaran, and also thank you to everyone who took the time to read my post and help me out. I really appreciate it. Thanks, Gregg

passing a timestamp datatype column to a variable and back

After several hours of trying, I trow the towel in the ring and come here to ask a question.

Source system uses a timestamp column in the transaction tables. which is equal to a non-nullable binary(8) datatype (sql 2000 bol).

What I want to do is get the timestamp at the start of the transfer and at the end of the transfer of data. and store these in a controltable

I try to do this in 2 sql execute tasks:

sqltask 1: "select @.@.DBTS AS SourceTimestamp" and map the resultset to a variable. Here come's the first problem what variable type to take ?

DBNULL works (meaning it doesn't give errors) (BTW: is there a way to put a variable as a watch when debugging sql tasks ?)

INT64 and UINT64 don't work error message that types for column and parameter are different

STRING works

Then I want to store this variable back in a table of a different data source

sqltask2: "insert into controltable values(getdate(), ?)" and make an input parameter that takes the previous timestamp ...

if I took DBNULL as a type for the variable there doesn't seem to be a single parameter type that works ?

if i take STRING as a type for the variable I have to modify the sql to do the explicit conversion from string to binary so I change CAST(? as binary). It doesn't return any error but the value stored in the table is 0x00000000000 and not the actual timestamp.

Any help on this one ? Why are the INT64/Bigint not working here, you can perfectly do a convert(bigint, timestampfield) in sql ?

How came the SQL datatypes, and the variable datatypes, parameter datatypes are so badly alligned to each other (and all seem to use different names) ?

tx for any help

Dirk

After some more hours (It just kept anoying me till late in the evening) I finaly found a way to make this work.

Make the variable in SSIS of type string

To store a timestamp as a variable

select Cast(timestampcolumn as bigint) as outputcolumn from controltable

create result set to map outputcolumn to variable

(SSIS wil do the conversion to string itself) Know that I tried to include this in the sql a convert to bigint an then to string but didn't work.

To use the timestamp in your queries or write it to another table

select * from table where timestampcolumn >= CAST(? AS BIGINT)

parameter mapping variable input type VARCHAR

(SSIS wil to the conversion from bigint to binary(8) itself

Got this working.

Point stays that datatypes should be more alligned between all the different places we use them, scripts, expressions, variables, parameters, sql data types...

Anyone got a better way, let me know.

tx

Dirk

|||

SSIS can genertae timestamps without an external data source. Is that not an option for you?

-Jamie

|||

Hi Jamie,

Don't think so. Let me explain what the purpose of all this was. The source ERP system uses timestamp columns on the tables. So I would like to use these columns as ModificationTime for my incremental load of new data in the DWH.

My problem was that I needed to get the @.@.DBTS from the source ERP system (hence the first SQL task) so that I would know the timestamp from the moment the upload started (my datapumps have a where clause like WHERE ModificationTimestamp >= TimestampOfStartLastSuccesfullUpload, so it will get all new and changed records since the start of the last upload).

Then I needed to store this Timestamp in my control table of the DWH and that's the 2d SQLtask.

I would have been extremly simple if the controltable was in the source ERP system, I could just insert the @.@.DBTS, but that was not an option. So I needed to pass the timestamp between the 2 SQL tasks. And that was my problem, getting the timestamp from the first select SQL task store it in a variable and pass that variable to the second insert sql task.

Perhaps this would have been easier in a script task, but I'm not that good at cooding. I haven't passed the level of copy, paste and modify some code ;-).

What do you think ? Is there a simpler solution ?

Dirk

|||

Ah I understand now. You need to persist the max timestamp between executions. That's a common requirement of course.

I may be mistaken but it seems the format of the timestamp is somewhat proprietary, hence the solution that you have come up with should be the most suitable - it sounds as though it will work fine though.

Question. What does the timestamp value look like? i.e. Can you paste it up here?

Regards

Jamie

|||

Dirk Van der Straeten wrote:

(BTW: is there a way to put a variable as a watch when debugging sql tasks ?)

Yes, there is. Drag the variable into a Watch window within the BIDS environment. When you execute and break you will be able to look at the value of the variable.

-Jamie

|||

Jamie,

Following select on the database and resultset.

select @.@.dbts as timestamp, CAST(@.@.dbts as bigint) as ConvertedInteger, CAST(@.@.dbts as varchar) as ConvertedVarchar

0x00000000000F94C7 1021127 _

The Timestamp is a binary(8) that is non nullable. But you can also interpreted is a an 8 byte integer which is the same as a bigint. Where __ is actualy the blanc of the string. That was the problem that converting directly from timestamp into the string variable did not work. Strangly I also didn't get it to work when I made the Variable a UI64 or I64 and then user LARGE_INTEGER as parameter type. He gave the error that the types where not compatible.

Got the watch thing figured out now.Tx.

/Dirk

Passing a selected row column value to the stored procedure

I have a simple Gridview control that has a delete command link on it.

If I use the delete SQL code in line it works fine. If I use a stored procedure to perform the SQL work, I can't determine how to pass the identity value to the SP. Snippets are below...

The grid
<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="SqlDataSource2">
<Columns>
<asp:BoundField DataField="member_id" HeaderText="member_id" InsertVisible="False"
ReadOnly="True" SortExpression="member_id" />
<asp:BoundField DataField="member_username" HeaderText="member_username" SortExpression="member_username" />
<asp:BoundField DataField="member_firstname" HeaderText="member_firstname" SortExpression="member_firstname" />
<asp:BoundField DataField="member_lastname" HeaderText="member_lastname" SortExpression="member_lastname" />
<asp:BoundField DataField="member_state" HeaderText="State" SortExpression="member_state" />
<asp:CommandField ShowEditButton="True" />
<asp:CommandField ShowDeleteButton="True" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:rentalConnectionString1 %>"
SelectCommand="renMemberSelect" SelectCommandType="StoredProcedure"
DeleteCommand="renMemberDelete" DeleteCommandType="StoredProcedure"
OldValuesParameterFormatString="original_{0}"
>

<DeleteParameters>

<asp:Parameter Name="member_id" Type="Int32" />

</DeleteParameters>

</asp:SqlDataSource
the SP

CREATE PROCEDURE renMemberDelete
@.member_id as int
As UPDATE [renMembers]
SET member_status=1
WHERE [member_id] = @.member_id
GO

Try:GridView2.DataKeyNames="member_id"

or

<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="SqlDataSource2" DataKeyNames="member_id">

passing a column of values to the stored procedure

how can i send a column of values to the stored procedure for filtering that stored procedure values...?

Hi,

have either a look here:

http://www.sommarskog.se/share_data.html

or if you have a delimited string to pass to the prcoedure you can take the function here to chop the values out and get a table with values back that you can join.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320221&SiteID=1

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Passing a column into a stored proc?

I'm writing a simple voting script and have columns for each options. I need to update the data based on whichever option the user picks.

I.e...

If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1
If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1
Etc., etc.

What's the best way to do that without building an ad-hoc SQL statement? There could be many options so I dont want to have lots of redundant SQL statements.

Can I just use a varible in a stored proc and do something like this?

UPDATE mytable SET @.optionUserpicked=@.optionUserpicked + 1

Thanks in advance

You can't really.

The best way is to redesign your table, so that it looks like this:

VoteID / Option (or optionID) / Votes

1,1,0

1,2,0

Then you can execute something like this:

UPDATE MyTable SET votes=votes+1 WHERE VoteID=1 ANDOption=@.option

Assuming that you are going to have multiple "polls", each uses a different VoteID. Each poll can then also have a variable number of options. It will also make reporting the final results easier as well.

|||

Maybe we can make a trick using dynamic SQL. For exampe:

create table myTable (UID int identity(1,1),option1 int,option2 int,option3 int)
go
INSERT INTO myTable (option1,option2,option3) SELECT 0,0,0
go
CREATE PROCEDURE sp_UpdVote @.opName sysname='option1',@.pkCol sysname='UID'
AS
IF (@.opName=@.pkCol)
RAISERROR('Can''t update the primary key',16,1)
ELSE
IF (exists(SELECT name FROM syscolumns
WHERE id=OBJECT_ID('myTable') ANDname=@.opName))
EXEC('UPDATE myTable SET ['+@.opName+']= ['+@.opName+']+1')
ELSE RAISERROR('There is no column named [%s] in this table.',16,1,@.opName)
go

EXEC sp_UpdVote

go
SELECT * FROM myTable

pass Column Name using Parameter in SQL Statement...

Hi,

I am trying to Pass Column Name(FieldName) using Parameter in SQL
Statement... But i am getting error...

how can i pass Column name using parameter?

Example:

in table i have fieldname ECountry...

Select @.FName='ECountry'
Select @.FName from Table...

How it works?

Thanx in Advance,
Regards,
Raghu...(raghutumma@.gmail.com) writes:

Quote:

Originally Posted by

I am trying to Pass Column Name(FieldName) using Parameter in SQL
Statement... But i am getting error...
>
how can i pass Column name using parameter?
>
Example:
>
in table i have fieldname ECountry...
>
Select @.FName='ECountry'
Select @.FName from Table...
>
How it works?

Why would you do it in the first place? Given a well-designed database,
the request does not make very much sense. But if you have a less well-
designed database, you need to do:

SELECT CASE @.paramname
WHEN 'thatfield' THEN thatfield
WHEN 'thisfield' THEN thisfield
WHEN 'leftfield' THEN leftfield
END
FROM tbl

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>how can i pass Column name using parameter? <<

You don't do this; it is bad programming. A well-designed module of
code returns a predictable result. What you have is what I call a
"Britney Spears, Squids and Automobiles" module, since it can return
anything of any data type!

Get a book on basic Software Engineering and read about coupling and
cohesion before you do any more programming in any language.|||On Oct 19, 9:06 am, --CELKO-- <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

how can i pass Column name using parameter? <<

>
You don't do this; it is bad programming. A well-designed module of
code returns a predictable result. What you have is what I call a
"Britney Spears, Squids and Automobiles" module, since it can return
anything of any data type!
>
Get a book on basic Software Engineering and read about coupling and
cohesion before you do any more programming in any language.

Maybe they weren't responsible for the database design, but are being
asked query from it due to business requirements or maybe it's not a
critical application and it's was easier to load a flat file into
Access than to design a proper normalized database. Regardless, it's
a legitimate question (as are your concerns about unpredictable
results), but to assume that Raghu doesn't know basic Software
Engineering is rude.|||<paulschultz54@.gmail.comwrote in message
news:1193191383.235885.139270@.y27g2000pre.googlegr oups.com...

Quote:

Originally Posted by

On Oct 19, 9:06 am, --CELKO-- <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>how can i pass Column name using parameter? <<

>>
>You don't do this; it is bad programming. A well-designed module of
>code returns a predictable result. What you have is what I call a
>"Britney Spears, Squids and Automobiles" module, since it can return
>anything of any data type!
>>
>Get a book on basic Software Engineering and read about coupling and
>cohesion before you do any more programming in any language.

>
Maybe they weren't responsible for the database design, but are being
asked query from it due to business requirements or maybe it's not a
critical application and it's was easier to load a flat file into
Access than to design a proper normalized database. Regardless, it's
a legitimate question (as are your concerns about unpredictable
results), but to assume that Raghu doesn't know basic Software
Engineering is rude.

You know, Joe has written several books, SQL for Smarties comes to mind.

However, one book that I doubt he'll ever be asked to write is "Joe Celko's
Guide to Winning Friends and Influencing People."

:-)

Quote:

Originally Posted by

>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Or... Practical SQL Solutions in the Real World.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.comwrote in message
news:13hubgltianpsb8@.corp.supernews.com...

Quote:

Originally Posted by

<paulschultz54@.gmail.comwrote in message
news:1193191383.235885.139270@.y27g2000pre.googlegr oups.com...

Quote:

Originally Posted by

>On Oct 19, 9:06 am, --CELKO-- <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

>>how can i pass Column name using parameter? <<
>>>
>>You don't do this; it is bad programming. A well-designed module of
>>code returns a predictable result. What you have is what I call a
>>"Britney Spears, Squids and Automobiles" module, since it can return
>>anything of any data type!
>>>
>>Get a book on basic Software Engineering and read about coupling and
>>cohesion before you do any more programming in any language.

>>
>Maybe they weren't responsible for the database design, but are being
>asked query from it due to business requirements or maybe it's not a
>critical application and it's was easier to load a flat file into
>Access than to design a proper normalized database. Regardless, it's
>a legitimate question (as are your concerns about unpredictable
>results), but to assume that Raghu doesn't know basic Software
>Engineering is rude.

>
You know, Joe has written several books, SQL for Smarties comes to mind.
>
However, one book that I doubt he'll ever be asked to write is "Joe
Celko's Guide to Winning Friends and Influencing People."
>
:-)
>
>

Quote:

Originally Posted by

>>

>
>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com
http://www.greenms.com/sqlserver.html
>
>

Pass a "Begin...End" Block from ASP

Is it okay to pass a Begin...End block to Sql Server from an ASP web page?
I
have a situation where one of my tables contains the column names that I nee
d
to select from another table. I have always used two separate select
statements (with two separate trips to the db) to get the values I need, but
I recently found that I can accomplish the same thing by passing a
Begin...End block like this...
begin
declare @.col_list varchar(8000)
select @.col_list = coalesce(@.col_list + ', ', '') +
approverlabel from approvers
where formid=6 order by approverorder
exec('select ' + @.col_list + ' from formconfigs where pid=2701')
end
Is there a reason why this should not be done? I realize this would be
better if it was implemented in a stored procedure.Yes this would be best in a stored procedure so it can reuse the query plan.
But in any case you don't need a BEGIN - END. If you send it as one batch
it will work fine.
Andrew J. Kelly SQL MVP
"creed1" <creed1@.discussions.microsoft.com> wrote in message
news:75660A09-951F-4D34-88BE-BFC26E13FED7@.microsoft.com...
> Is it okay to pass a Begin...End block to Sql Server from an ASP web page?
> I
> have a situation where one of my tables contains the column names that I
> need
> to select from another table. I have always used two separate select
> statements (with two separate trips to the db) to get the values I need,
> but
> I recently found that I can accomplish the same thing by passing a
> Begin...End block like this...
> begin
> declare @.col_list varchar(8000)
> select @.col_list = coalesce(@.col_list + ', ', '') +
> approverlabel from approvers
> where formid=6 order by approverorder
> exec('select ' + @.col_list + ' from formconfigs where pid=2701')
> end
> Is there a reason why this should not be done? I realize this would be
> better if it was implemented in a stored procedure.

Partitioning using Date - TimeKey (Ref to another table) column

Hello.
I'd like to implement table partitioning for one of my tables in my DB
(Sales History). I'd like to setup partitioning using a date. I'm planning
to use a partition per year that are going to be spread among multiples
Filegroups. This table contains an integer field that link to my TimeKey
table to get the Date of the transaction. I would know how to setup
partitioning if my date was directly in my table but since I have to join to
another table, how can I achieve this? I can't use a range from my integer
since they're not really sorted sequentially. Any help would be
appreciated. Thanks!"Christian Hamel" <chamel@.notyourbusiness.com> wrote in message
news:e8gEWeTEGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hello.
> I'd like to implement table partitioning for one of my tables in my DB
> (Sales History). I'd like to setup partitioning using a date. I'm
> planning to use a partition per year that are going to be spread among
> multiples Filegroups. This table contains an integer field that link to
> my TimeKey table to get the Date of the transaction. I would know how to
> setup partitioning if my date was directly in my table but since I have to
> join to another table, how can I achieve this? I can't use a range from
> my integer since they're not really sorted sequentially. Any help would
> be appreciated. Thanks!
>
Not what you want to hear but I'd be inclined to allocate an intelligent
time key to start with. If it's keyed on date only then use the date as an
8-digit number in the form yyyymmdd.
Possibly you could create a computed column that derives a date from your
key and use that as your partitioning column.
David Portas
SQL Server MVP
--|||I DON'T WANT TO HEAR THAT!
:)
That's what I thought, it is not a big deal since I'm @. the beginning of
implementing our new DW using SQL Server 2005 so I will definitively
consider your "intelligent" key suggestion. Thanks!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a crit dans le
message de news: OQMwB1TEGHA.376@.TK2MSFTNGP12.phx.gbl...
> "Christian Hamel" <chamel@.notyourbusiness.com> wrote in message
> news:e8gEWeTEGHA.532@.TK2MSFTNGP15.phx.gbl...
> Not what you want to hear but I'd be inclined to allocate an intelligent
> time key to start with. If it's keyed on date only then use the date as an
> 8-digit number in the form yyyymmdd.
> Possibly you could create a computed column that derives a date from your
> key and use that as your partitioning column.
> --
> David Portas
> SQL Server MVP
> --
>