How to run sql server script in xBasic?

[WindForce]

Hi
I have stuff like the following, saved in a .sql file on a sql server 2008 db:

DECLARE @Location		CHAR(3),
	@SCode				CHAR(4),
	@ReportStartDate	DATETIME,
	@ReportEndDate		DATETIME,
	@Date				DATETIME;

SELECT @Location = 'CDG',
	@SCode = 'IZAR',
	@ReportStartDate = '5/1/2014',
	@ReportEndDate = '7/31/2014';

CREATE TABLE #Date ([Date] DATETIME, DateString VARCHAR(5));

SELECT @Date = @ReportStartDate;

WHILE @Date <= @ReportEndDate
BEGIN
	INSERT INTO #Date ([Date], DateString)
	VALUES (@Date, REPLACE(CONVERT(VARCHAR(6), @Date, 100), ' ', ''));

	SELECT @Date = DATEADD(DAY, 1, @Date);
END

How can i run this from xBasic in a Ajax callback? What if i want @ReportStartDate and @ReportEndDate to be from arguments?

Thanks
Jaime

[compuaid]

Re: How to run sql server script in xBasic?

There was a similar discussion regarding using arguments and return values from stored procedures here:
How doI grab MS-SQL Server autoincrement value after INSERT

[Peter.Greulich]

Re: How to run sql server script in xBasic?

Jamie,

Have you seen this?

http://www.downloads.alphasoftware.c...SQLTables.html

You don't even need to watch the videos. It's a great tutorial.

[WindForce]

Re: How to run sql server script in xBasic?

Yes Peter, i even have enough time to forget half of it <g>

Thanks Brad, it works!

BTW, while you sql gurus are here, given the following dynamic pivot query, what syntax should i use to save the pivotted table ( pvt in my query ) into a new table in my db?

SELECT @DynamicPivotQuery =
N'SELECT Station, CarGroup, CarNo,' + @ColumnName +
'FROM
(
	SELECT R.PickUpStation as Station, R.CarGroup, R.CarNo, REPLACE(CONVERT(VARCHAR(6), D.[Date], 100), '' '', '''') AS DateString
	FROM dbo.FleetTrack AS R
	INNER JOIN #Date AS D
		ON R.PickUpDate <= D.[Date]
		AND R.DropOffDate >= D.[Date]
	) AS Reservation
PIVOT
(
	COUNT(DateString)
	FOR DateString IN (' + @ColumnName + ')
) AS pvt
ORDER BY pvt.Station, pvt.CarGroup, pvt.CarNo'

[mixter]

Re: How to run sql server script in xBasic?

Interesting question, I think you will need to create the table first, then insert your pivotted data following the creation of the table.

http://community.spiceworks.com/topi...ored-procedure

http://dba.stackexchange.com/questio...an-hold-the-re

http://social.msdn.microsoft.com/For...um=transactsql