November 13, 2006 at 9:17 am
In Access, I have written SQL that retrieves data from several tables. This SQL accesses vbscript functions in a .bas file. I want to move from Access to SQL Server. After importing the data into SQL Serve tables, is there a way I can use these vbcript functions in SQL Server using the same .bas file/vbscript?
Thanks
Drew
November 14, 2006 at 1:52 am
You can run the xp_cmdshell extended stored procedure and pass in a command line string to execute your vbscript
However, if your vbscript returns information from your tables in your database, then writing a SQL statement is by far the most efficient (and probably easiest) way of getting your data.
November 14, 2006 at 2:22 am
I thought it could be done by maybe putting the script in an activex step in DTS.
So this vbscript has functions that concatenates input parameters (amongst other things). The SQL Script then calls those functions with the columns names of a tables as the parameters and so produces the required string (concatenated column values).
To do this in SQL Server, would I create UDFs, and apply the same principle?
Many thanks
Drew
November 14, 2006 at 5:41 am
It might be helpful if you posted the VBScript and/or the SQL so we could see what it's doing.
November 14, 2006 at 7:09 am
Drew, what your describing sounds like it will fit well with a UDF. It may also work just as in-line sql in an update or insert statement too.
I agree with Dick that a short posting will probably help draw a true concensus though.
Regards,
Matt
November 14, 2006 at 7:36 am
It sounds to me that what you need is to use in MS_SQL dynamic query. That is:
DECLARE @sql_batch AS nvarchar(4000)
DECLARE @table AS nvarchar(128)
DECLARE @field AS nvarchar(128)
SELECT @table='Categories', @field='CategoryName'
SET @sql_batch='SELECT '+@field+' FROM '+@table+' ORDER BY '+@field
EXEC (@sql_batch)
You can build a table-valued function or a stored procedure to which you could pass the @table, @field parameters to get your results. Definetely you don't have tu use external resources like vbScript for this.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
November 14, 2006 at 7:59 am
Thanks for the posts.
I have managed to achieve the required using UDFs. There are so many ways of using UDFs, I really didn't know where to start. Here's a sample of what I ended up doing:
-------------------------------
The function:
ALTER Function [dbo].[formatline4_SQL]
(@locality As varchar(50),
@town
As varchar(50))
Returns varchar
(50)
AS
Begin
Declare
@line4 varchar(50)
If (@locality <> '')
Begin
Select @line4 = @locality
End
Else
Begin
Select @line4 = @town
End
Return
@line4
End
-------------------------------
The SQL:
Select
dbo
.FormatLine4_SQL (
GMS_PR_ST_DESC
.LOCALITY_NAME,
GMS_PR_ST_DESC
.TOWN_NAME
)
AS LINE4
From TheTable
November 14, 2006 at 8:39 am
Eliminate the UDF here and ave the overhead.
An inline case statement will work for you.
SELECT CASE WHEN GMS_PR_ST_DESC.LOCALITY_NAME = ""
THEN GMS_PR_ST_DESC.TOWN_NAME
ELSE GMS_PR_ST_DESC.LOCALITY_NAME
END AS TownLocality
FROM .......
Regards,
Matt
November 14, 2006 at 9:07 am
You got me scratching my head. But in others there are concatanations like so...
ALTER
Function [dbo].[formatline3_SQL]
(@flag varchar(50),
@paostart
varchar(50),
@paostartsfx
varchar(50),
@paoend
varchar(50),
@paoendsfx
varchar(50),
@streetname
varchar(50),
@streettype
varchar(50))
Returns
varchar (50)
AS
Begin
Declare
@line3 varchar(50)
If
(@paostart <> '')
Begin
Select @line3 = @paostart
End
If
(@paostartsfx <> '')
Begin
Select @line3 = @line3 + @paostartsfx
End
If
(@paoend <> '')
Begin
Select @line3 = @line3 + '-' + @paoend
End
If
(@paoendsfx <> '')
Begin
Select @line3 = @line3 + @paoendsfx
End
If
(@line3 <> '')
Begin
Select @line3 = @line3 + ' ' + @streetname
End
Else
If Not (@flag = 'POSTAL' And @streettype = '2')
Begin
Select @line3 = @streetname
End
Return
@line3
End
Can this be done without a udf? (you don't have to rewrite the whole thing)
November 14, 2006 at 9:58 am
Sure it can, but that one maybe better off as a udf. The levels of nested cases could get ugly. You could use case statements in your UDF, make it easier to read.
The trade off is simplicity (reuse) vs. overhead of going to the UDF each time.
My only concern for your UDf is that your return value is only 50, if all your imputs are 50 your output could be 350.
Regards,
Matt
November 14, 2006 at 10:11 am
So it is. I'll change it.
Thanks for that,
Drew
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply