Easy Query

Hi,
This is a really simple query and for the life of me I cant remember how to
do it
Name Table
Name
Philip
Steve
Contact Table
Name Position Contact
Philip 1 01244
Philip 2 0777
Steve 2 01352
Steve 3 00000
Join the 2 tables based on name and get the contact number from the second
table based upon the lowest value of the position field,
so the select results would look like
Philip 01244
Steve 01352
Any help would be great, thanks PDUse MIN() agregate fumction along GROUP BY clause
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:902B8202-1189-4503-9498-B57BC7B1D451@.microsoft.com...
> Hi,
> This is a really simple query and for the life of me I cant remember how
> to
> do it
> Name Table
> Name
> Philip
> Steve
> Contact Table
> Name Position Contact
> Philip 1 01244
> Philip 2 0777
> Steve 2 01352
> Steve 3 00000
> Join the 2 tables based on name and get the contact number from the second
> table based upon the lowest value of the position field,
> so the select results would look like
> Philip 01244
> Steve 01352
> Any help would be great, thanks PD
>

Dynamically selecting a row from a table

I have a function which i want to return where an id in a table exists somethign like
if exists (select id from @.tablename where id = @.id)
You cannot use dynamic SQL within TSQL UDFs. You should also use dynamic SQL with care. It has security and performance implications if used improperly. Why would you want to write a UDF that does if exists() check on any table? Isn't it easy just to write the query wherever you need it because that will be optimized better. You could consider writing this as a SP instead. And for the dynamic SQL to work you need to grant SELECT permissions for all users on the tables that you would check.

DYNAMIC TSQL

Here is the sample query:

DECLARE @.TABLENAME NVARCHAR(50);

DECLARE @.COL NVARCHAR(50);

DECLARE @.VALUE NVARCHAR(50);

/*** THESE VARIABLES WERE ARE BEING DYNAMICALLY FEEDED THROUGH CURSOR***/

DECLARE @.SQL1 NVARCHAR(1000);

SET SQL1 = 'SELECT * FROM' + @.TABLENAME + ' WHERE' + @. COL + '=" + @.VALUE

EXECUTE sp_executesql @.SQL1

Now when I execute this SP it gives me error invalid colunm name. I figure out that its because of the variable @.VALUE. Eventually I found out that I need to single quote the value of this dynamically feeded variable @. VALUE

is there any way I can do this; give quotes to @.value like @. COL + '=" + ' @.VALUE'

Use the following query,

Code Snippet

DECLARE@.TABLENAME NVARCHAR(50);

DECLARE@.COL NVARCHAR(50);

DECLARE@.VALUE NVARCHAR(50);

DECLARE@.SQL1 NVARCHAR(1000);

DECLARE@.PARAM NVARCHAR(1000);

SET @.SQL1= N'SELECT * FROM ' + @.TABLENAME + N' WHERE ' + @.COL + N'=@.VALUE';

SET @.PARAM = N'@.Value as Nvarchar(50)';

EXECUTEsp_executesql @.SQL1, @.PARAM, @.VALUE

dynamic tablename issue

Dear all:
I have a storedprocedure for db loader form some temp tables.
my storedprocedure as the following:

declare c cursor
for
select * from @.tablename
open c
fetch c into ...

but I get an error for declare cursor for dynamic tablename,

did sql server has some BIF to evaluate the variable for table name?

thanks for your kindly assistance.

regards,

Stanley Huanguse the dynamic sql statments EXEC(@.sql_statement) or sp_executesql see the Holy BOL|||Originally posted by yoavmaimon
use the dynamic sql statments EXEC(@.sql_statement) or sp_executesql see the Holy BOL
do you mean I can write the stored procedure as:

create proc sp_dynamicTableName
(
declare @.tablename varchar(20)
)
as
declare c cursor
for
exec('select * from ' & @.tablename)
open c
fetch c into ...

regards for your reply

thanks

Stanley Huang|||what u wrote wont work,
1. u must build the sql statement out of th exec
2. all the sql statements that uses the @.tablename variable must be dynamic.
3. why cursors?|||Originally posted by yoavmaimon
what u wrote wont work,
1. u must build the sql statement out of th exec
2. all the sql statements that uses the @.tablename variable must be dynamic.
3. why cursors?

Dear Sir:
Why cursor?
Because I have to parse the records and and by the columns to do some extra action.
After actions, and I will use Waitfor the pause the cursor engine to limit the resource of this stored procedure.
Do you know how to set the priority of specified account in sql server,
then I can avoid to use cursor.

regards,

Stanley Huang

Dynamic Tablename

Hi,
Maybe a simple Question
I have a Dataset with following SQL Command:
SELECT a.*, b.stelleText
FROM verkauf_leas200510 a LEFT OUTER JOIN
Vregion_stelle b ON a.stellelevel = b.stelleLevel AND
a.stellekey = b.stelleKey
WHERE (a.stellelevel = @.pStelleLevel) AND (a.stellekey = @.pStelleKey)
ORDER BY a.stellelevel, a.stellekey, a.sort
Is it possible to change theTablename (verkauf_leas200510 ) also
dynamically, maybe also with a parameter like in the where-clause
Have not found a solution yet, because i want to generate a report with the
tablename as parameter.
Thanks in advance
DieterIf you can use a stored procedure as a datasource for the report you can
solve the problem by creating dynamic SQL.
The Proc will have 3 parameters:
@.Tablename
@.pStelleLevel
@.pStelleKey
And in the proc you will dynamically create the select statement
Grtz,
Nico
"Dieter Felix" wrote:
> Hi,
> Maybe a simple Question
> I have a Dataset with following SQL Command:
> SELECT a.*, b.stelleText
> FROM verkauf_leas200510 a LEFT OUTER JOIN
> Vregion_stelle b ON a.stellelevel = b.stelleLevel AND
> a.stellekey = b.stelleKey
> WHERE (a.stellelevel = @.pStelleLevel) AND (a.stellekey = @.pStelleKey)
> ORDER BY a.stellelevel, a.stellekey, a.sort
> Is it possible to change theTablename (verkauf_leas200510 ) also
> dynamically, maybe also with a parameter like in the where-clause
> Have not found a solution yet, because i want to generate a report with the
> tablename as parameter.
> Thanks in advance
> Dieter|||Table name is not a problem. However, you need to have the field names
returned stay the same. To do this have the query tool in generic mode. Then
you put in an expression
="select * from " & parameters!TableName.value
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dieter Felix" <Dieter Felix@.discussions.microsoft.com> wrote in message
news:6D6DE04F-8F74-418E-9EB2-E32E7437F0E1@.microsoft.com...
> Hi,
> Maybe a simple Question
> I have a Dataset with following SQL Command:
> SELECT a.*, b.stelleText
> FROM verkauf_leas200510 a LEFT OUTER JOIN
> Vregion_stelle b ON a.stellelevel = b.stelleLevel AND
> a.stellekey = b.stelleKey
> WHERE (a.stellelevel = @.pStelleLevel) AND (a.stellekey = @.pStelleKey)
> ORDER BY a.stellelevel, a.stellekey, a.sort
> Is it possible to change theTablename (verkauf_leas200510 ) also
> dynamically, maybe also with a parameter like in the where-clause
> Have not found a solution yet, because i want to generate a report with
> the
> tablename as parameter.
> Thanks in advance
> Dieter

dynamic table name in from clause

Hello All,
I am trying to create UDF that will take in tablename and columnname,
maxlength as parameters. Based on the tablename and columnname, I want to
return the length of the longest columndata. If the length value is bigger
than the maxlength parameter, I pass in , I just want to return the
maxlength.
Basically, I am trying to do the following:
alter FUNCTION dbo.rp_MaxColumnLength
(@.TableName varchar(200),@.ColumnName varchar(200),@.MaxLenth INT)
RETURNS INT
AS
BEGIN
DECLARE @.ColMaxLength INT
SELECT @.ColMaxLength = MAX(LEN(@.ColumnName)) FROM @.TableName
if @.colmaxlegth > @.Maxlength
return @.MaxLength
else
return @.colmaxlength
END
But I guess, I cannot use a variable in the FROM clause as a tablename.
Does anyone know a workaround?
Please help.
Thanks, sqlgirlYou might want to start with the following article:
http://www.sommarskog.se/dynamic_sql.html
It has some relevant details and implications of using such approaches.
Anith|||
Hey Amith,
Thanks a bunch. I was able to solve my problem by looking at the
article.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Dynamic table name from varchar field

Hi,
How can i execute folowing T-SQL properly ?
Error given due to so.name is a varchar value.
Select Distinct so.name as TableName,(Select count(*) from so.name) as
RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
so.xtype='U'
The output will be "
TableName RecCount
-- -- --DMP wrote:
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id
> where so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
Erland covers this here:
http://www.sommarskog.se/dynamic_sql.html
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||You can't execute dynamic SQL inline like that, read up on EXECUTE()
fortunately a rowcount is available in sysindexes that you can use without
traversing each table anyway:
SELECT SysObjects.Name,
SysIndexes.Rows
FROM SysObjects
JOIN SysIndexes ON SysIndexes.ID=SysObjects.ID AND SysIndexes.IndID IN
(0,1)
WHERE SysObjects.xtype='U'
for reference IndID in (0,1) eliminates all indexes but the base tables
0=heaped, 1=clustered. Note that queries on the system tables are likely to
fail if you upgrade to a new version of SQL.
Mr Tea
http://mr-tea.blogspot.com
"DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
news:eEI%236wTAFHA.1084@.tk2msftngp13.phx.gbl...
> Hi,
> How can i execute folowing T-SQL properly ?
> Error given due to so.name is a varchar value.
> Select Distinct so.name as TableName,(Select count(*) from so.name) as
> RecCount from syscolumns sc inner join sysobjects so on sc.id=so.id where
> so.xtype='U'
> The output will be "
> TableName RecCount
> -- -- --
>

Dynamic Stored Procedures uses vars only

Hi there,

I would like to know how to create Dynamic stored procedure which defines TableName as a Variable and return all fields from this Table.

And also how to Dynamicly create a sp_GetNameByID (for instance)

using vars only.

Thanks

It would be very helpfull to me if you could give links of Dynamic SQL tutorials from which i can learn.

Writing dynamic T-SQL doesn't strike me as being relevant to SSIS so I'm a little confused. Perhaps you could elaborate.

By the way, best practice stipulates that you shouldn't name your sprocs "sp_*".

-Jamie

dynamic sql w/sp_executesql - servername parameter issue

I know that we can't pass serername/tablename as a parameter to sp_executesq
l
as follow...
But I have to work around sql injection vulnerability too...
I want to use some type of paratertize way but...can't find a solution yet.
Please help!!
create proc SaferDynamicSQL(@.serverName nvarchar(25))
as
declare @.sql nvarchar(255)
set @.sql = 'select @.p_serverName+'..'+'action from action'
exec sp_executesql @.sql,
N'@.p_serverName nvarchar(25)',
@.p_serverName = @.serverName
goIf you want to avoid injection then why do this dynamically in TSQL? You
could create views that reference the linked server(s) and then reference
the view by name. You could parameterize the connection string in your
client application. You could use UDL files to abstract the server name.
David Portas
SQL Server MVP
--

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