I have function, which returns the dataType of some column in some table:

declare @columnType nVarchar(50)

set @columnType=dbo.colType('myTable','myColumn')

It works, the result is for example 'varchar(50)'.

Now, I would like to declare variable of this column type.
What is the best way to do this?

DECLARE @myVariable @columnType

Something like:

if @columnType='int'
    DECLARE @myVariable int
else if @columnType='datetime'
    DECLARE @myVariable datetime
....and so on

Any idea?

regards,Simon

simonZ (simon.zu***@studio-moderna.com) writes:
Show quoteHide quoteIn Oracle you can declare a variable to have the same type as a table
column. There is no such feature in SQL Server.

What you can use is alias data types. In SQL 2000 you say:

   EXEC sp_addtype 'mytype', 'varchar(50)'

In SQL 2005 there is proper DML syntax for this:

   CREATE TYPE mytype FROM varchar(50)

In both cases, you can use this type both in the table defintion and
for variable declarations.

Hi, Erland

it works now but I still have one problem.

If I create my dataType inside stored procedure:

EXEC sp_addtype 'myType', 'varchar(50)'

and then try to use it in the same procedure:
declare @orderResult myType

I get an error:

Cannot find data type myType

If I create myType before I call the procedure, than it works.
But can I do this inside the same procedure?

regards,
Simon


Show quoteHide quote

simonZ (simon.zu***@studio-moderna.com) writes:
Show quoteHide quoteNo, you can't and there would be very little reason to do it. Types
are like tables, things you define when you design and model your database,
and you only change/add types and tables in conjunction with major
upgrades of the application. It's nothing you add dynamically.

What exactly are you trying to achieve? Maybe sql_variant xould be of help to
you.

OTH, perhaps you should explain your requirements in more detail.

I hope you're not trying to develop a single universal solution to handle
several highly specialised cases that could actually be handled more
efficiently by several specialised solutions.


ML

Hi,

well, I'll try to explain. I'm creating custom SQL paging. I don't know what
type is the column using for sort.

So, I read the type of that column and than create myType, which is the same
as the type of order column.

Then I can execute the procedure:

set @sqlS=N'select @c='+@order+' '+@sql
exec sp_executesql @sqlS,N'@c myType output', @c=@orderResult output

If I read the type of the column and append it as string to sql statement,
won't work:

declare columnType nvarchar(100)
set @columnType='int' --for example, I get this from my function

exec sp_executesql @sqlS,N'@c '+columnType+' output', @c=@orderResult output

Any other idea?

Otherwise, now everything work, just that I have to create myType(which is
the same type as sort column) before I call procedure for sql paging.

Regards,Simon

Show quoteHide quote

simonZ (simon.zu***@studio-moderna.com) writes:
Show quoteHide quoteIn practice, how many types do you need to handle? Let's see: int, nvarchar
and datetime, that would be it. Maybe bigint as well. I mean, that
order column would never be float or uniqueidentifier would it?

So just use IF statements to handle the few types that are involed.

Or look at the type sql_variant.

Hi, Erland

What is the best way here:

this statement works:
exec sp_executesql N'SELECT @c=column1 FROM table ORDER BY column1',N'@c
varchar(10) output',@c=@orderResult output

Because I don't know which column user wants to be ordered, I put ordered
column  into variable of stored procedure

declare @orderColumn nVarChar(50)

Let say that order column is column1:
set @orderColumn='column1'

For executeSql statement I need to know which type is that column. So I
created function which returns me the type of that column as nVarChar(50):

declare @columnType nVarChar(50)

Set @columnType=myFunctionType(@orderColumn)

and I get in this case: @columnType='varchar(10)'

So, I can write now the executeSql statement:

exec sp_executesql N'SELECT @c='+@orderColumn+' FROM table ORDER BY
'+@orderColumn,N'@c '+@columnType+' output',@c=@orderResult output

But this sp_executeSql won't work. It's the same as the first one except
that I have some values in variables.Is it possible to rewrite this
statement somehow to work?

So, I moved step forward and created myType which is the same as the column
type of ordered column and write the following statement:
exec sp_executesql N'SELECT @c='+@orderColumn+' FROM table ORDER BY
'+@orderColumn,N'@c myType output',@c=@orderResult output

This works, but the problem is that I have to create myType before I execute
this procedure.

One workaround is IF statement for each type. But I have 3 sp_executeSql
statement and there would be a lot of statements.
That was the erason I cerated myType - you know, to be more intelligent :)

I haven't heard for sql_variant - it's really nice, but does it work ok? I
don't have any experience with that.

Here I have another question:

I heard that executeSql statement is prepared in cache and even if the sql
statement is changed, the executeSql sometimes executes the one from the
cache, not the new one.
That could be a big critical problem. Is it possible to force executeSQL not
to store the sql statement in cache?

Thank you for your help,

Regards,Simon

Show quoteHide quote

simonZ (simon.zu***@studio-moderna.com) writes:
Show quoteHide quoteNo. At least to nothing that would be meaningful.

And the user will typically not have privileges to create types.

Furthermore, also @orderResult has to be of this type. Else there could
be interest conversion problems when the type comes back.

Then go and have a look at it! I can't say for sure that it will work
for you, because I feel that missing some pieces in what you are up to.
But right now you are in a dead end that's taking you nowhere.

A completely different alternative would be to simply insert the
selected data into a table with a sessionid as key. You would bounce
the data over a temp table with an IDENTITY column to get rows numbered.
(Unless you are on SQL 2005, in which case you would use the row_number()
function.) Then you can serve pages rows from that table. This also has
the nice side-effect that you don't requery when the user pages.

This is incorrect. The statement text as submitted is matched against
the statement texts in the cache and they must match exact, including
on case and spacing.

Joining 2 select statement with like fields
Views and CTEs performance problem
Adding a space in a field
Can't use Shared Memory protocol to connect
merge two date ranges with overlap from two tables