SQL

Save MySQL Output to a Text File

Posted by | 2 Comments

I have just found out a nifty little trick regarding MySQL output the other day. I just had to put this up as a reminder to myself, and also to share with the viewers.

Let say, you want to have the output of your SELECT statement created to a text file. The MySQL database server could be sitting on a different box (or the same machine, but that doesn’t really matter). All you have to do is issue a command in your terminal:

echo "select * from MyTable" | mysql -h mydatabaseserver -u myusername -pmypassword mydatabase > output.txt

That’s it! Just replace the <MyTable>to your table of choice (or just replace the whole SELECT statement to suit your needs), <mydatabaseserver> to your database server name, <myusername> to your database user name, <mypassword> to your password, and <mydatabase> to your database.
The piping ‘>’ will redirect your output to a text file on your desired location.

SQL Select Statement with Comma-Separated List Condition

Posted by | 4 Comments

I have one problem the other day; Why does my “SELECT * FROM tbl WHERE col IN (@list)” does not work? The col datatype is int, while the @list parameter is a comma-separated varchar.

The Problem
In a stored procedure, I’m composing a SELECT statement with a WHERE clause in which the column condition datatype is integer, while the parameter supplied to it has the datatype of varchar:

CREATE PROCEDURE get_product_names @ids varchar(50) AS
SELECT ProductID, ProductName
FROM Northwind..Products
WHERE ProductID IN (@ids)

ProductID is int, @ids is a comma-separated string, i.e.: ‘9,12,27,37’.

When try to call:
EXEC get_product_names '9,12,27,37'

But this fails with:
Server: Msg 245, Level 16, State 1, Procedure get_product_names, Line 2
Syntax error converting the varchar value '9,12,27,37' to a column of data type int.

Then comes the solution, convert the comma-separated string to a recordset in a temporary table.

The Solution
There’s quite a number of solutions out there, but I’ve found a pretty good ways along with a very comprehensive explainations here. The one that I’m implementing is called the Iterative Method.

Create a new User Defined Function:

CREATE FUNCTION iter_intlist_to_table (@list ntext)
      RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                          number  int NOT NULL) AS
   BEGIN
      DECLARE @pos      int,
              @textpos  int,
              @chunklen smallint,
              @str      nvarchar(4000),
              @tmpstr   nvarchar(4000),
              @leftover nvarchar(4000)

      SET @textpos = 1
      SET @leftover = ''
      WHILE @textpos <= datalength(@list) / 2
      BEGIN
         SET @chunklen = 4000 - datalength(@leftover) / 2
         SET @tmpstr = ltrim(@leftover + substring(@list, @textpos,
                                 @chunklen))
         SET @textpos = @textpos + @chunklen

         SET @pos = charindex(',', @tmpstr)
         WHILE @pos > 0
         BEGIN
            SET @str = substring(@tmpstr, 1, @pos - 1)
            INSERT @tbl (number) VALUES(convert(int, @str))
            SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1,
                                 len(@tmpstr)))
            SET @pos = charindex(',', @tmpstr)
         END

         SET @leftover = @tmpstr
      END

      IF ltrim(rtrim(@leftover)) <> ''
         INSERT @tbl (number) VALUES(convert(int, @leftover))

      RETURN
   END

Modify the Stored Procedure to the one below:

CREATE PROCEDURE get_product_names @ids varchar(50) AS
      SELECT ProductID, ProductName
      FROM   Northwind..Products
      WHERE  ProductID IN (SELECT number FROM
                       dbo.iter_intlist_to_table(@ids))
   go

Execute the Stored Procedure:
EXEC get_product_names_iter '9,12,27,37'

Now the stored procedure will happily accepts the comma-separated varchar parameter, and returns the records needed. Is there any other method to this? Feel free to share. 🙂