Custom query string question

Hi there,

I have a custom query string that I placed as a varchar, for instance

@.UnknownColumnName varchar(100)
@.CostumQueryString varchar(1000)
@.Value int

SET @.UnknownColumnName = (SELECT ColName FROM Utility WHERE ID =1)

SET @.CustomQueryString = 'SELECT ' + @.UnknownColumnName + ' From Table2 WHERE .....'

I have to execute this custom querystring now:
SET @.Value = (EXEC sp_executesql @.CustomQueryString)

I get a syntax error....I'm not sure how i can execute this querystirng and set the result to a variable..any help much appreciated

Regards
Mike

you can try this one

SET @.CustomQueryString = 'SELECT ' + @.UnknownColumnName + ' INTO #Values From Table2 WHERE .....'

EXEC sp_executesql @.CustomQueryString

SELECT *
FROM #Values

DROP TABLE #Values|||Hi, thanks for the reply.

I actually found the solution in books online (ooops should have searched harder there) and the best way of doing this is to make use of OUTPUT parameters:

DECLARE @.IntVariable int;
DECLARE @.SQLString nvarchar(500);
DECLARE @.ParmDefinition nvarchar(500);
DECLARE @.max_title varchar(30);

SET @.IntVariable = 197;
SET @.SQLString = N'SELECT @.max_titleOUT = max(Title)
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @.level';
SET @.ParmDefinition = N'@.level tinyint, @.max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @.SQLString, @.ParmDefinition, @.level = @.IntVariable, @.max_titleOUT=@.max_title OUTPUT;

SELECT @.max_title;

Custom 'Order By' Function?

Hi I have a column varchar(4). Users enter values in one of 5 formats -
1) 1,2,3,4...10,11
2) 01,02,03,04...10,11
3) A1,A2,A3,B1,B2,B3...B10,B11
4) 1.1,2.1,3.1.....10.1,11.1
5) 1.1, 1.1A, 1.1B, 2.1, 2.1A, 2.1B....10.1,10.1A
The queruies that select from this table will only select records with
one of the formats at any one time. Is it possible to ensure the order
is always logically correct based on numerical and alphabetical
ordering, as above?
So far its seems ok except formats 4 & 5 where I get the folowoing
output-
1.3 1.3A 1.3P 11.3 16.3 2.3 2.3P 2.3S
Thanks
hals_leftHi
your query will work fine if ur 4 and 5 looks similar to 2.
the results in 4 & 5 are considered and sorted as per the char value.
prefix 0 ans see the results.
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"hals_left" wrote:

> Hi I have a column varchar(4). Users enter values in one of 5 formats -
>
> 1) 1,2,3,4...10,11
> 2) 01,02,03,04...10,11
> 3) A1,A2,A3,B1,B2,B3...B10,B11
> 4) 1.1,2.1,3.1.....10.1,11.1
> 5) 1.1, 1.1A, 1.1B, 2.1, 2.1A, 2.1B....10.1,10.1A
> The queruies that select from this table will only select records with
> one of the formats at any one time. Is it possible to ensure the order
> is always logically correct based on numerical and alphabetical
> ordering, as above?
> So far its seems ok except formats 4 & 5 where I get the folowoing
> output-
> 1.3 1.3A 1.3P 11.3 16.3 2.3 2.3P 2.3S
> Thanks
> hals_left
>|||On 28 Jul 2005 08:00:54 -0700, hals_left wrote:

>Hi I have a column varchar(4). Users enter values in one of 5 formats -
>
>1) 1,2,3,4...10,11
>2) 01,02,03,04...10,11
>3) A1,A2,A3,B1,B2,B3...B10,B11
>4) 1.1,2.1,3.1.....10.1,11.1
>5) 1.1, 1.1A, 1.1B, 2.1, 2.1A, 2.1B....10.1,10.1A
Hi hals_left,
How does one store 10.1A in a varchar(4) column?

> is thgere now way to write a different
>ordering function?
Try the following. It's not pretty, but it might work:
ORDER BY
CASE
WHEN my_column LIKE '[A-Z]%'
THEN LEFT (my_column, 1)
END,
CASE
WHEN my_column LIKE '[A-Z]%'
THEN CAST (SUBSTRING (my_column, 2, 3) AS int)
WHEN my_column LIKE '%.%'
THEN CAST (LEFT (my_column, CHARINDEX ('.', my_column) - 1) AS int)
ELSE CAST (my_column AS int)
END,
CASE
WHEN my_column LIKE '%.%'
THEN SUBSTRING (my_column, CHARINDEX ('.', my_column) + 1, 4)
END
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

custom datatype SQL server 2000

Hi All
In our DB we have info in a format of "150:1" resp "nnn:n". Its information
about Contrast Ratio.
This is of course typed as varchar. That prevents us from sorting the fields
the way we need. we are also
unable to do any kind of 'simple' compare < >
I guess in 2005 we could use custom Data Type feature. Is there any
workaround in MSSQL 2000
Thanx for any hints...
PetttA ratio (rational number) can be expressed as a decimal number. You could
add a computed column to your tabe that represents the contrast ratio as a
decimal
ALTER TABLE [YourTable]
ADD [DecimalRatio]
AS CAST(LEFT([ContrastRatio], CHARINDEX(':',[ContrastRatio]) - 1) AS numeric)
/
CAST(RIGHT([ContrastRatio], LEN([ContrastRatio]) -
CHARINDEX(':',[ContrastRatio])) AS numeric)
"Petr SIMUNEK" wrote:

> Hi All
> In our DB we have info in a format of "150:1" resp "nnn:n". Its informatio
n
> about Contrast Ratio.
> This is of course typed as varchar. That prevents us from sorting the fiel
ds
> the way we need. we are also
> unable to do any kind of 'simple' compare < >
> I guess in 2005 we could use custom Data Type feature. Is there any
> workaround in MSSQL 2000
> Thanx for any hints...
> Pettt
>
>|||Petr
Why not split the values into 2 columns? You could then build a sequence or
do a comparison using a quotient of the 2 values, right? You can always
combine them back to ratio notation for display purposes.
CREATE TABLE Foobar (
key_col VARCHAR(3) NOT NULL PRIMARY KEY
,contrast_num INT NOT NULL
,contrast_den INT NOT NULL
);
INSERT INTO Foobar VALUES ('AAA', 150, 1);
INSERT INTO Foobar VALUES ('BBB', 20, 1);
INSERT INTO Foobar VALUES ('CCC', 1, 1);
INSERT INTO Foobar VALUES ('DDD', 43, 2);
INSERT INTO Foobar VALUES ('EEE', 25, 6);
SELECT
key_col
,CAST(contrast_num AS VARCHAR(3)) + ':' + CAST(contrast_den AS CHAR(1))
AS contrast_ratio
FROM Foobar
ORDER BY contrast_num / contrast_den
;
Comparisons could work the same way.
Joe