Padding partial table name with @variable value

Is there a way to this in SQL
declare @.partial_table_name varchar(128)
set @.partial_table_name = 'mytable'
select * from customer
into tbl_cust_@.partial_table_name
Above i am inserting the values from customer to table
tbl_cust__@.partial_table_name. I want to pass the @.partial_table_name value
to the table so that table name becomes tbl_cust_mytable. This is so that I
can dynamically insert into a table based opon conditions in my store proc.
Any help would be appreciated.
ThanksEXEC('SELECT * INTO tbl_cust_'+@.partial_table_name+' from customer');
I'd be very careful about where @.partial_table_name gets created. SQL
injection can yield to inadvertent statements being executed, such as:
SELECT * INTO tbl_cust_foo FROM (SELECT a=1); UPDATE Employees SET Salary =
Salary * 10 WHERE FullName='Malicious Employee'; SELECT * from customer;
Please give the following article a thorough read:
http://www.sommarskog.se/dynamic_sql.html
"b_dba" <b_dba@.discussions.microsoft.com> wrote in message
news:CB37F1E5-5C85-46B6-B443-6F504E1CD69D@.microsoft.com...
> Is there a way to this in SQL
> declare @.partial_table_name varchar(128)
> set @.partial_table_name = 'mytable'
> select * from customer
> into tbl_cust_@.partial_table_name
> Above i am inserting the values from customer to table
> tbl_cust__@.partial_table_name. I want to pass the @.partial_table_name
> value
> to the table so that table name becomes tbl_cust_mytable. This is so that
> I
> can dynamically insert into a table based opon conditions in my store
> proc.
> Any help would be appreciated.
> Thanks|||Thanks Aaron,
That was very helpful.Its doing what I wanted to do. I would be careful as
where the variable is created.
Appreciate your quick response.
cheers,
b_dba
"Aaron Bertrand [SQL Server MVP]" wrote:

> EXEC('SELECT * INTO tbl_cust_'+@.partial_table_name+' from customer');
> I'd be very careful about where @.partial_table_name gets created. SQL
> injection can yield to inadvertent statements being executed, such as:
> SELECT * INTO tbl_cust_foo FROM (SELECT a=1); UPDATE Employees SET Salary
=
> Salary * 10 WHERE FullName='Malicious Employee'; SELECT * from customer;
> Please give the following article a thorough read:
> http://www.sommarskog.se/dynamic_sql.html
>
>
>
> "b_dba" <b_dba@.discussions.microsoft.com> wrote in message
> news:CB37F1E5-5C85-46B6-B443-6F504E1CD69D@.microsoft.com...
>
>

Padd Character Function To Return Variable Type

All,
I have a UDF for SQL that will padd a varchar value to a specific width and
align left or right. I found this code online somewhere, but the problem is
that it accpets a value up to Varchar(8000) and returns a varchar(8000). I
am writing a query to upload to a mainframe and need fixed-width fields, but
each field is not the same size. I can pass in the correct width to the
function and it padds correctly, but in a DTS transformation to a text file,
it thinks all fields are 8000 characters wide.
I am asking if it is possible to return a variable size data type instead of
a fixed type.
Here is the orig function:
ALTER FUNCTION [dbo].[PaddChar] (
@.ValueToPad varchar(8000),
@.PadCharacter char(1),
@.Justification bit,
@.Width int
)
/ ****************************************
***********************************
*************************
This function allows the USER TO pass IN a string / character value AND it
will padd the value according TO given parameters. The parameters are
AS follows:
@.ValueToPad = Value to be padded BY function.
@.PadCharacter = Character used TO pad a given value.
@.Justification = Justification format bit
0 - Value will be RIGHT justified after padding.
1 - Value will be LEFT justified after padding.
@.Width = Total COLUMN width OF the output after padding.
----
--
NOTE - ALL VALUES passes IN FOR padding must be OF CHAR or
VARCHAR data type.
****************************************
************************************
*************************/
RETURNS varchar(8000) AS
BEGIN
DECLARE @.x int
IF @.ValueToPad IS NULL SET @.ValueToPad = ''
IF @.PadCharacter IS NULL SET @.PadCharacter = ''
SET @.X = @.Width - LEN(@.ValueToPad)
--Right Justify Value
IF @.Justification = 0
BEGIN
SET @.ValueToPad = REPLICATE(@.PadCharacter,@.X) + @.ValueToPad
END
ELSE
--Left Justify Value
BEGIN
SET @.ValueToPad = @.ValueToPad + REPLICATE(@.PadCharacter,@.Width)
END
RETURN @.ValueToPad
END
and what I want basically and which doesn't seem to work, is:
ALTER FUNCTION [dbo].[PaddChar] (
@.ValueToPad varchar(8000),
@.PadCharacter char(1),
@.Justification bit,
@.Width int
)
/ ****************************************
***********************************
*************************
This function allows the USER TO pass IN a string / character value AND it
will padd the value according TO given parameters. The parameters are
AS follows:
@.ValueToPad = Value to be padded BY function.
@.PadCharacter = Character used TO pad a given value.
@.Justification = Justification format bit
0 - Value will be RIGHT justified after padding.
1 - Value will be LEFT justified after padding.
@.Width = Total COLUMN width OF the output after padding.
----
--
NOTE - ALL VALUES passes IN FOR padding must be OF CHAR or
VARCHAR data type.
****************************************
************************************
*************************/
RETURNS varchar(@.Width) AS
BEGIN
DECLARE @.x int
IF @.ValueToPad IS NULL SET @.ValueToPad = ''
IF @.PadCharacter IS NULL SET @.PadCharacter = ''
SET @.X = @.Width - LEN(@.ValueToPad)
--Right Justify Value
IF @.Justification = 0
BEGIN
SET @.ValueToPad = REPLICATE(@.PadCharacter,@.X) + @.ValueToPad
END
ELSE
--Left Justify Value
BEGIN
SET @.ValueToPad = @.ValueToPad + REPLICATE(@.PadCharacter,@.Width)
END
RETURN @.ValueToPad
END
Does anyone have any ideas on this?
Thanks,
NathanWhen I need to export fixed-width fields my approach is a bit
different. I create a view that formats each column the way I need it
to a fixed length, then export from the view. Examples of formatting:
convert(char(30), ItemName) as ItemName
STR(ItemValue,9,2)
Roy|||Thank you, I rarely use CHAR since it does padd for storage in the database,
duh! That worked to replace my instances of the dbo.PaddChar in my query fo
r
all the intstances where I am just padding on the right with spaces.
However, I had to just wrap a couple of them that I am right aligning, or
padding with 0's.
Since my query only pulls back a few rows a day the performance isn't bad.
Thank you again,
Nathan
"Roy Harvey" wrote:

> When I need to export fixed-width fields my approach is a bit
> different. I create a view that formats each column the way I need it
> to a fixed length, then export from the view. Examples of formatting:
> convert(char(30), ItemName) as ItemName
> STR(ItemValue,9,2)
> Roy
>

p/c selfjoin question

Hi,
I need some help on a specific self join question.
I have a parent/child table:
CREATE TABLE [TAB_PC_KST] (
[Element] [varchar] (255) NULL ,
[Parent] [varchar] (255) NULL ,
[EType] [char] (1) NULL ,
[Weight] [float] NULL ,
) ON [PRIMARY]
GO
where EType is the element type and can be 'C' (for consoloidated element)
or 'N' (non consolidated element).
Lets say in that table is the following content:
Element Parent EType Weight
All Members <NULL> C <Null>
First Child All Members C 1
Second Child All Members N 1
First Sub First Child N 1
Second Sub First Child N 1
That will give a structure like the following
All Members
|--First Child
|--First Sub
|--Second Sub
|--Second Child
What I need is a select on that table, with the following result set
EType Element Weight
C All Members <NULL>
<NULL> First Child 1
<NULL> Second Child 1
C First Child <NULL>
<NULL> First Sub 1
<NULL> Second Sub 1
Is that possible without an cursor on all C-Elements? Perhaps with selfjoin
or
subqueries?
Thanks for any help!
Cheers
ThomasThomas
Look at greate script (example) written by Itzik Ben-Gan
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
"Thomas Seidel" <tseidel@.misag.com> wrote in message
news:%234qjvclSFHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I need some help on a specific self join question.
> I have a parent/child table:
> CREATE TABLE [TAB_PC_KST] (
> [Element] [varchar] (255) NULL ,
> [Parent] [varchar] (255) NULL ,
> [EType] [char] (1) NULL ,
> [Weight] [float] NULL ,
> ) ON [PRIMARY]
> GO
> where EType is the element type and can be 'C' (for consoloidated element)
> or 'N' (non consolidated element).
> Lets say in that table is the following content:
> Element Parent EType Weight
> All Members <NULL> C <Null>
> First Child All Members C 1
> Second Child All Members N 1
> First Sub First Child N 1
> Second Sub First Child N 1
> That will give a structure like the following
> All Members
> |--First Child
> |--First Sub
> |--Second Sub
> |--Second Child
> What I need is a select on that table, with the following result set
> EType Element Weight
> C All Members <NULL>
> <NULL> First Child 1
> <NULL> Second Child 1
> C First Child <NULL>
> <NULL> First Sub 1
> <NULL> Second Sub 1
> Is that possible without an cursor on all C-Elements? Perhaps with
selfjoin
> or
> subqueries?
> Thanks for any help!
>
> Cheers
> Thomas
>|||On Tue, 26 Apr 2005 14:04:23 +0200, Thomas Seidel wrote:
(snip)
>What I need is a select on that table, with the following result set
>EType Element Weight
>C All Members <NULL>
><NULL> First Child 1
><NULL> Second Child 1
>C First Child <NULL>
><NULL> First Sub 1
><NULL> Second Sub 1
>Is that possible without an cursor on all C-Elements? Perhaps with selfjoin
>or
>subqueries?
Hi Thomas,
The query below will not return the exact same set as above; the
differrence is basically formatting that's better done at the client side.
(It can be done in SQL - but it ain't pretty and it won't run quick)
SELECT a.Element, b.Element, b.Weight
FROM TAB_PC_KST AS a
INNER JOIN TAB_PC_KST AS b
ON b.Parent = a.Element
WHERE a.EType = 'C'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)